MySQL Forums
Forum List  »  Optimizer & Parser

Re: select distinct slower in 5.0 than 4.1
Posted by: Timour Katchaounov
Date: December 29, 2005 01:55AM

Hi Saar,

I got a bit confused by the two explains in your last mail when I compared
them to your initial message. In your first message you say that 5.0 chooses
tight index scan ("range"), while the 5.0 explain in your last mail shows
loose index scan ("Using index for group-by").

Could you please do this:
- clarify under which circumstances which method is chosen,
- run the same query in 5.0 but use "force index (BUCKET_lastname_ATT)"
to force the usage of the PK index,
- send the complete (unedited) EXPLAIN output for the cases you can
reproduce, including the case above with FORCE INDEX,
- for each query send the execution times.
- run the following queries and tell us the result:
- select count(*) from bucket_lastname;
- select count(distinct PID) from bucket_lastname;

Thank you,
Timour

> CREATE TABLE `bucket_lastname` (
> `PID` int(10) unsigned NOT NULL default '0',
> `lastname` char(32) default NULL,
> UNIQUE KEY `BUCKET_lastname_CONST`
> (`PID`,`lastname`),
> KEY `BUCKET_lastname_ATT` TYPE BTREE
> (`lastname`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>
> explain select distinct(pid) from bucket_lastname
> where lastname = 'Smith'
>
> Result with 4.1.12
> 1,"SIMPLE","bucket_lastname","ref","BUCKET_lastnam
> e_ATT","BUCKET_lastname_ATT",33,"const",709,"Using
> where; Using temporary"
>
> Result with 5.0.16
> 1,"SIMPLE","bucket_lastname","range","BUCKET_lastn
> ame_ATT","BUCKET_lastname_CONST","",8,"Using
> where; Using index for group-by"

--
Timour Katchaounov, Software Engineer - Optimizer
MySQL AB, www.mysql.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3032
December 18, 2005 12:24PM
2413
December 19, 2005 10:49AM
2090
December 20, 2005 11:36AM
Re: select distinct slower in 5.0 than 4.1
2136
December 29, 2005 01:55AM


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.