MySQL Forums
Forum List  »  Optimizer & Parser

Re: indices not used
Posted by: Martin Luy
Date: June 19, 2010 03:35AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2766
May 25, 2010 04:13AM
1591
May 25, 2010 12:07PM
1490
May 27, 2010 08:35PM
Re: indices not used
1432
June 19, 2010 03:35AM
1396
June 21, 2010 06:25PM
1336
June 22, 2010 01:15AM


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.