MySQL Forums
Forum List  »  Optimizer & Parser

select distinct slower in 5.0 than 4.1
Posted by: Saar Barhoom
Date: December 18, 2005 12:24PM

I have the following very simple statement:
select distinct(PID) from lastnames where name = 'Smith'

The table has only two columns, PID and name. It has a BTREE index on the name column, and a unique contraint on the PID, name columns combination. The storage engine is MYISAM.

On 4.1.16 this query runs very fast. Using the EXPLAIN SELECT I see that the 'type' of the query is 'ref'.
On 5.0.16 this query runs very slow (about x20 times slower). EXPLAIN SELECT show that the 'type' of the query is 'range'.

Could that be related to the loose index scan feature of 5.0?
Have I forgotten anything when doing the migration?

Thanks,
Saar

Options: ReplyQuote


Subject
Views
Written By
Posted
select distinct slower in 5.0 than 4.1
3404
December 18, 2005 12:24PM
2648
December 19, 2005 10:49AM
2314
December 20, 2005 11:36AM


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.