RE: MySQL uses PRIMARY key when it should? use another index

[Rick James]

I would be surprised if using t1ba is ever the 'right' way to do the
query.  The "(10)" virtually destroys any usefullness of the index.  And
since you asked for "a,b", it could really be satisfied in the index.  I
do find it puzzling that it says both "PRIMARY" and "Using index".

> -----Original Message-----
> From: Vasil Dimov [mailto:vasil.dimov@stripped] 
> Sent: Wednesday, January 02, 2008 7:03 AM
> To: MySQL Internals
> Subject: MySQL uses PRIMARY key when it should? use another index
> 
> CREATE TABLE `t1` (
>   `a` int(11) NOT NULL DEFAULT '0',
>   `b` blob NOT NULL,
>   `c` text NOT NULL,
>   `d` text NOT NULL,
>   PRIMARY KEY (`a`,`b`(255),`c`(255)),
>   KEY `b` (`b`(767)),
>   KEY `t1ba` (`b`(10),`a`)
> ) ENGINE=InnoDB;
> 
> EXPLAIN SELECT a FROM t1 ORDER BY b;
> 
>  
> +----+-------------+-------+-------+---------------+---------+
> ---------+------+------+-----------------------------+
>  | id | select_type | table | type  | possible_keys | key     
> | key_len | ref  | rows | Extra                       |
>  
> +----+-------------+-------+-------+---------------+---------+
> ---------+------+------+-----------------------------+
>  |  1 | SIMPLE      | t1    | index | NULL          | PRIMARY 
> | 518     | NULL |    1 | Using index; Using filesort | 
>  
> +----+-------------+-------+-------+---------------+---------+
> ---------+------+------+-----------------------------+
> 
> The result is the same even for the command:
> 
> EXPLAIN SELECT b,a FROM t1 FORCE INDEX FOR ORDER BY (t1ba) 
> ORDER BY b,a;
> 
> Some time ago it would have used the t1ba index. I am trying 
> to find the
> changeset that changed this behavior but am experiencing some 
> problems,
> here is what I did:
> 
> * get a list of the "recent" changesets with "bkf changes" on 
> the latest
> bk://mysql.bkbits.net/mysql-5.1 tree (same list can be retrieved via
> the BK web interface).
> 
> * did a "binary search" in that list by:
>   bkf clone -r... bk://mysql.bkbits.net/mysql-5.1 mysql-5.1
>   compile
>   test
>   repeat for another revision.
> 
> The outcome is that the tree, cloned with -r1.2604.71.1 has the "old"
> behavior (t1ba index is used) while the tree cloned with -r1.2635.3.5
> has the "new" behavior. The thing is that both trees differ in 7692
> lines. I would expect they to differ only by the change that was
> introduced in 1.2635.3.5 (one line fix) because they are 
> adjacent in the
> list of changesets:
> http://mysql.bkbits.net:8080/mysql-5.1/?DATE=-4w..&PAGE=changes
> 
> What am I missing here?
> 
> How can I further dig and find the changeset that caused the 
> behavior to
> change?
> 
> -- 
> Vasil Dimov
> moc.elcaro@stripped        Software Developer @ Oracle/Innobase Oy
> gro.DSBeerF@dv                Committer @ FreeBSD.org
> gro.d5v@dv                    Home @ Sofia, Bulgaria
>