I have some sort of problem too... Look at this: mysql> explain select * from game3 order by memberid; +-------+------+---------------+------+---------+------+------+------------- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+------------- ---+ | game3 | ALL | NULL | NULL | NULL | NULL | 1245 | Using filesort | +-------+------+---------------+------+---------+------+------+------------- ---+ 1 row in set (0.02 sec) mysql> explain select * from game3 where memberid = '1'; +-------+-------+---------------+----------+---------+-------+------+------- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+----------+---------+-------+------+------- + | game3 | const | memberid | memberid | 2 | const | 1 | | +-------+-------+---------------+----------+---------+-------+------+------- + 1 row in set (0.00 sec) As you can clearly see in the 2nd query, memberid is a key, and is used there. But why isn't it used in the ORDER BY clause? Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353 ----- Original Message ----- From: "Lukas Knecht" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 11, 2001 11:48 AM Subject: Again: ORDER BY key_part1,key_part2 does not use index > 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:ipomopcphmhamkmig oob#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#M ySQL_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 > > --------------------------------------------------------------------- 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
