Just out of interest....
If you are selecting * then you are using all the records....
What if you use are 'where'......?
Simon
-----Original Message-----
From: Lukas Knecht [mailto:[EMAIL PROTECTED]]
Sent: 11 June 2001 10:49
To: [EMAIL PROTECTED]
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
