MySQL Forums :: Optimizer & Parser :: multicolumn index & forcing index to avoid filesort


Advanced Search

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 3147 Paolo Barroni 07/04/2010 09:30AM
Re: multicolumn index & forcing index to avoid filesort 1455 Rick James 07/05/2010 11:58PM


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.