Re: indices not used

[Martin Luy]

thanks for your answer. Two more 2 questions because I can't get rid of filesort:


question 1:

now I have the following index:
keyname INDEX 2755177 c1, c1, c3, factor


SELECT COUNT(*) FROM `products` WHERE c1= 22 AND c2 = 5 AND c3 = 2
COUNT(*)
38742

(there are 5510354 rows in the table)


EXPLAIN(SELECT * FROM `products` WHERE c1 = 22 AND c2 = 5 AND c3 = 2 ORDER BY factor DESC)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE products ref keyname keyname 10 const,const 59524 Using where; Using filesort


Why is filesort used? Only 0.7% of the rows were selected, and the columns of the query exactly match the index.



question 2:

It's not necessary that the results are strictly sorted. Executing this query once a day would be ok:

ALTER TABLE product ORDER BY factor;

Does it help to disable keys before this query and enable afterwards?



Edited 1 time(s). Last edit at 06/19/2010 03:35AM by Martin Luy.