Re: select distinct slower in 5.0 than 4.1
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