Hi, I know this subject has been treated several times in this list (see e.g. http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:sss:65398:200102:ipomopcphmhamkmigoob#b ), but I think it deserves further discussion. My problem is the following: I have a rather large table (~430M records in 21G with a 4.4G packed index) created by: CREATE TABLE T ( `Id` int(11) NOT NULL default '0', `Id2` int(11) NOT NULL default '0', `F1` smallint(6) default NULL, `F2` smallint(6) default NULL, `F3` smallint(6) default NULL, `F4` smallint(6) default NULL, `F5` float default NULL, `F6` mediumtext, `F7` float default NULL, `F8` float default NULL, KEY `TIds`(`Id`,`Id2`), KEY `TId2`(`Id2`) ) TYPE=MyISAM MAX_ROWS=2500000000 PACK_KEYS=1; The query I want to run is: SELECT * FROM T ORDER BY Id, Id2; I would expect MySQL to use the index to retrieve the rows in sorted order, but this is not what happens (MySQL 3.23.38): EXPLAIN SELECT * FROM T ORDER BY Id, Id2; +-------+------+---------------+------+---------+------+-----------+----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+-----------+----------------+ | T | ALL | NULL | NULL | NULL | NULL | 431476682 | Using filesort | +-------+------+---------------+------+---------+------+-----------+----------------+ "Using filesort" means that MySQL does the sorting itself without using the index. I can think of two possible implementations: (1) Create a temporary 21G file with 430M complete records, sort these by (Id, Id2) and then read the data sequentially from this file. The sorting can be done "on the fly" using a good mergesort implementation and will use almost no random accesses with large enough buffers. I expect the time required for this to be the time to read, write and reread 21G: with 50MB/s I/O bandwidth, 3*21G/50MB ~ 21 min. With the CPU time required for the sorting, I would expect less than an hour. The time to process the query would be dominated by reading the sorted data into the application, even with a Gigabit network. (2) Create a temporary 16*430M file with 430M records (Id, Id2, record pointer), sort this by (Id, Id2), and then retrieve the complete records doing 430M random accesses. From earlier experience, I assume this is what MySQL does, despite the fact that it is entirely equivalent to using the index... I expect the time rqeuired for this to be dominated by the 430M random reads. On a fast RAID with 2 ms avg access time, this may require up to 430M * 2ms ~ 10 days, but may be substantially less if the data file is already partially sorted by Id, Id2 (which it is in our case). For me, this discussion boils down to three questions: A) Does MySQL sort the entire file (1), or just the "ORDER BY" criteria with subsequent random reads (2)? B) If what I believe is correct (MySQL does (2)), why does it create a temporary file containing the keys and sort it instead of using the index? C) The MySQL manual in http://www.mysql.com/documentation/mysql/bychapter/manual_Performance.html#MySQL_indexes says: ...The following queries will use the index to resolve the ORDER BY part: SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; Isn't this information wrong or at least incomplete? Can anyone shed some light on this? Thanks. Lukas Knecht infox Neptunstr. 35 CH-8032 Zürich --------------------------------------------------------------------- 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
