MySQL Forums
Forum List  »  Performance

Should I remove indexes with cardinality=1 ?
Posted by: Arnaud Lesauvage
Date: July 25, 2008 08:39AM

Hi all !

We experienced very poor performance on a query.
This query is quite complex (4 tables and a subquery), so I won't post it here unless you really need it.

The point is that the query was running quite fast (~3 seconds), and just adding one "WHERE" criteria made it crawl almost to a stop.
We just added the criteria "AND table2.field3 = 1". This table2.field3 is indexed, and it appears that the cardinality of the index is 1 (~300k rows in the table, only two different values for this field).

What is surprising is that the query plan (showed by explain) is good in the first query, but when adding the new criteria, the table2 comes in first in the plan and the key "field3" is used.
How can this be, since the cardinality is 1 ? The planner should know that using this index is very bad, shouldn't it ?

I just dropped the bad index, and performance was back again.
So should I check all my indexes and remove all the ones with a low cardinality ?

Thanks for any advice !

Arnaud

Options: ReplyQuote


Subject
Views
Written By
Posted
Should I remove indexes with cardinality=1 ?
3204
July 25, 2008 08:39AM


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.