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

(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

Written By
May 25, 2010 04:13AM
May 25, 2010 12:07PM
May 27, 2010 08:35PM
Re: indices not used
June 19, 2010 03:35AM
June 21, 2010 06:25PM
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.