multicolumn index & forcing index to avoid filesort

[Paolo Barroni]

Hi all,

on MySQL 5.077 I have the following table:

car
------------
car_id INTEGER AUTONUMBER (PRIMARY)
brand CHAR(25)
adtype enum ('sell', 'rent', 'lease', 'buy'
published enum ('yes','no')
location CHAR(25)
price INTEGER
miles INTEGER
ranking INTEGER


and the following query:

SELECT SQL_CALC_FOUND_ROWS car_id FROM car
WHERE
brand='BMW' AND
adtype='sell' AND
published='yes' AND
(location='Barcelona' OR location='Madrid' OR location='Malaga' OR location='Cordoba' OR location='Cadiz') AND
(price>4000 AND price <10000) AND
(miles>40000 AND miles<200000)
ORDER BY ranking;

Now my problem is that I cant avoid the filesort on the ranking column.

I have a multicolumn index on brand,adtype,published,location,price,miles
and a separate index on ranking

I have also tried creating an index with ranking at the leftmost (ranking,brand,adtype,published,location,price,miles) and modifying the query:

SELECT SQL_CALC_FOUND_ROWS car_id FROM car
WHERE
ranking>0 AND
brand='BMW' AND
adtype='sell' AND
published='yes' AND
(location='Barcelona' OR location='Madrid' OR location='Malaga' OR location='Cordoba' OR location='Cadiz') AND
(price>4000 AND price <10000) AND
(miles>40000 AND miles<200000)
ORDER BY ranking;

I was hoping this would "fool" mysql into using the right index that would return the results in the right order on the first place


I also tried removing some of the columns at the right of the indices just in case this was confusing the optimizer.

Am i missing something on multi column indices?

any advice or best practices would be much appreciated!



Edited 1 time(s). Last edit at 07/04/2010 03:09PM by Paolo Barroni.