Hello mysql,
I was trying to load very huge (5GB 45mil rows) to the text dump in
sorted order (according to primary key).
The problem is mysql somehow does not wants to scan the table by
primary key to produce sorted output row by row, but prefers to use
filesort which would take quite a long time in this case:
mysql> explain select * from dominf.domip order by ip desc;
+-------+------+---------------+------+---------+------+----------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+----------+----------------+
| domip | ALL | NULL | NULL | NULL | NULL | 44840332 | Using filesort |
+-------+------+---------------+------+---------+------+----------+----------------+
1 row in set (1.10 sec)
mysql> explain select * from dominf.domip order by ip;
+-------+------+---------------+------+---------+------+----------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+----------+----------------+
| domip | ALL | NULL | NULL | NULL | NULL | 44840332 | Using filesort |
+-------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)
CREATE TABLE domip (
ip int(10) unsigned NOT NULL default '0',
domip char(80) NOT NULL default '',
tz tinyint(4) NOT NULL default '0',
fldom tinyint(3) unsigned NOT NULL default '0',
ts timestamp(14) NOT NULL,
rt tinyint(4) unsigned NOT NULL default '0',
LCHECKED timestamp(14) NOT NULL,
CHANGED tinyint(3) unsigned NOT NULL default '0',
network int(11) NOT NULL default '-1',
PRIMARY KEY (ip)
) TYPE=MyISAM;
diamond:/spylog/db # mysqladmin processlist | grep -v Sleep
+----+-------+-------------+---------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
| |
+----+-------+-------------+---------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 6 | titan | ss23.local | counter | Query | 0 | statistics | SELECT
|page_id,title FROM st90.g00pages WHERE page_prot=1 AND page_domain=11884 AND
|page='close.php3 |
| 39 | root | localhost | | Query | 195 | Sorting result | select * from
|dominf.domip order by ip desc into outfile "dominf.txt"
| |
| 48 | root | localhost | | Query | 0 | | show
|processlist
| |
+----+-------+-------------+---------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
