MYSQL order by insists on using filesort

I need to optimize a MYSQL query doing an order by. No matter what I do, mysql en开发者_开发问答ds 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.

---

Ok. Looks like swapping the columns in the index did the trick.
I don't really know why... maybe someone else has an explanation?

Either way, if I add an index

create index IDX_TBL_PROBE_DAILY_07 on TBL_PROBE_DAILY(BYTES,DAYSTAMP)   

then mysql favors IDX07 (even without the force index) and does an index sort instead of file sort.

---

I couldn't read the definition. Here it is formatted:

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 Query:

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;

I suspect the problem is that your query contains two range queries. I my experience, MySQL cannot optimise beyond the first range query it encounters, and so as far as it is concerned, any index beginning with DAYSTAMP is equivalent to any other.

The clue in the explain is key length: this shows how much of the index value actually gets used. It is probably the same value (3) even when you force it to use the index you want.

---

Using an open ended equality in where always forces a filesort. Simply put, an open ended < or > makes MySQL get the rows and order them to eliminate the ones not in matching your query. If logically this query can be changed into a range (between timestamp X and timestamp Y) THEN MySQL can use those bookend values to get results directly from the index and then either filesort if you still want the return sorted or not if you only want to match the values

---

Swapping did worked because

To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order. See Section 8.3.1.11, “ORDER BY Optimization”, and Section 8.3.1.12, “GROUP BY Optimization”.