MYSQL order by insists on using filesort.

Programmer Question

Hi All.

I need to optimize a MYSQL query doing an order by. No matter what I do, mysql ends up doing a filesort instead of using the index.

Here's my table ddl... (Yes, In this case the DAYSTAMP and TIMESTAMP columns are exactly the same).

CREATE TABLE DB_PROBE.TBL_PROBE_DAILY ( DAYSTAMP date NOT NULL, TIMESTAMP date NOT NULL, SOURCE_ADDR varchar(64) NOT NULL, SOURCE_PORT int(10) NOT NULL, DEST_ADDR varchar(64) NOT NULL, DEST_PORT int(10) NOT NULL, PACKET_COUNT int(20) NOT NULL, BYTES int(20) NOT NULL, UNIQUE KEY IDX_TBL_PROBE_DAILY_05 (DAYSTAMP,SOURCE_ADDR(16),SOURCE_PORT,DEST_ADDR(16),DEST_PORT,TIMESTAMP), KEY IDX_TBL_PROBE_DAILY_01 (SOURCE_ADDR(16),TIMESTAMP), KEY IDX_TBL_PROBE_DAILY_02 (DEST_ADDR(16),TIMESTAMP), KEY IDX_TBL_PROBE_DAILY_03 (SOURCE_PORT,TIMESTAMP), KEY IDX_TBL_PROBE_DAILY_04 (DEST_PORT,TIMESTAMP), KEY IDX_TBL_PROBE_DAILY_06 (DAYSTAMP,TIMESTAMP,BYTES) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(DAYSTAMP)) (PARTITION TBL_PROBE_DAILY_P20100303 VALUES LESS THAN (734200) ENGINE = InnoDB, PARTITION TBL_PROBE_DAILY_P20100304 VALUES LESS THAN (734201) ENGINE = InnoDB, PARTITION TBL_PROBE_DAILY_P20100305 VALUES LESS THAN (734202) ENGINE = InnoDB, PARTITION TBL_PROBE_DAILY_P20100306 VALUES LESS THAN (734203) ENGINE = InnoDB) */;

The partitions are daily and I've added IDX_TBL_PROBE_DAILY_06 especially for the query I'm trying to get working, which is:

select SOURCE_ADDR as 'Source_IP', SOURCE_PORT as 'Source_Port', DEST_ADDR as 'Destination_IP', DEST_PORT as 'Destination_Port', BYTES from TBL_PROBE_DAILY where DAYSTAMP >= '2010-03-04' and DAYSTAMP <= '2010-03-04' and TIMESTAMP >= FROM_UNIXTIME(1267653600) and TIMESTAMP <= FROM_UNIXTIME(1267687228) order by bytes desc limit 20;

The explain plan as follows:
+----+-------------+-----------------+---------------------------+-------+-----------------------------------------------+------------------------+---------+------+--------+-----------------------------+ | id | select_type | table |
partitions | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+-----------------+---------------------------+-------+-----------------------------------------------+------------------------+---------+------+--------+-----------------------------+ | 1 | SIMPLE | TBL_PROBE_DAILY |
TBL_PROBE_DAILY_P20100304 | range |
IDX_TBL_PROBE_DAILY_05,IDX_TBL_PROBE_DAILY_06 | IDX_TBL_PROBE_DAILY_05 | 3 | NULL |
216920 | Using where; Using filesort |
+----+-------------+-----------------+---------------------------+-------+-----------------------------------------------+------------------------+---------+------+--------+-----------------------------+

I've also tried to FORCE INDEX (IDX_TBL_PROBE_DAILY_06) , in which case it happily uses IDX_06 to satisfy the where constraints, but still does a filesort :(

I cant imagine index sorting impossible on partitioned tables? InnoDB behaves different to MyISAM in this regard? I would have thought InnoDBs index+data caching to be ideal for index sorting.

Any help will be much appreciated... I've been trying all week to optimize this query in different ways, without much success.

Find the answer here