MySQL Forums
Forum List  »  Optimizer & Parser

multicolumn index & forcing index to avoid filesort
Posted by: Paolo Barroni
Date: July 04, 2010 09:30AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
multicolumn index & forcing index to avoid filesort
3244
July 04, 2010 09:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.