I'm trying to eliminate a filesort from a very simple query but having no luck. Even though I've read the manual section on when indexes are used in conjunction with ORDER BY, it seems I just can't get an index to be used.
I'm running MySQL 3.23.58.
The table: CREATE TABLE `minifatboy` ( `p1date` date NOT NULL default '0000-00-00', `p2date` date NOT NULL default '0000-00-00', `struct` char(120) binary default NULL, PRIMARY KEY (`p2date`,`p1date`) ) TYPE=MyISAM
This table has just under 1 million rows. It has a "big brother" table called fatboy which has over 143 mil rows on which I'll be running the exact same query later, assuming I can get good performance out of this.
The query?
SELECT struct FROM minifatboy ORDER BY p2date, p1date;
An explain gives me:
mysql> explain select struct from minifatboy order by p2date, p1date;
+------------+------+---------------+------+---------+------+--------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------+------+---------------+------+---------+------+--------+----------------+
| minifatboy | ALL | NULL | NULL | NULL | NULL | 999370 | Using filesort |
+------------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
I feel like I have to be missing something obvious here. I don't want to have to wait while MySQL performs a filesort to sort the data into the order already specified by the primary key. It doesn't matter much for minifatboy, but for fatboy this means performing a filesort on a 31gb table. Is it just because I'm not restricting rows and therefore MySQL thinks it should just do a table scan? I know I can try to force the use of an index with MySQL 4, but I'd rather not upgrade if I don't have to (USE INDEX doesn't help, btw).
Thanks, --Ludwig Pummer
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
