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

[Sergei Golubchik]

Hi!

On Jan 02, Vasil Dimov wrote:
> 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 | 
> 
> +----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
> 
> 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?

You're missing the fact that changeset dependencies can only be fully
represented by a graph,

http://www.bitkeeper.com/gifs/revtool_repo.gif

and "adjacent" could only be applied to that graph. When "bk changes"
command squeezes the graph to a list, it cannot preserve adjacency
information for all changesets.
 
> How can I further dig and find the changeset that caused the behavior to
> change?

I don't know if bkf or web client can show dependencies.
Anyway, you can do the following: list all changesets between
1.2604.71.1 and 1.2635.3.5 and look for suspects. E.g.

http://mysql.bkbits.net:8080/mysql-5.1/?PAGE=changes&REV=1.2604.71.1..1.2635.3.5

or

http://mysql.bkbits.net:8080/mysql-5.1/?PAGE=cset&REV=1.2604.71.1..1.2635.3.5

You can continue your binary search on that list of course, but looking
for suspicious changesets would be faster.

Changeset 1.2573.81.1, with the comment
"
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys

  Problems: 
    1. ALTER TABLE ... ORDER BY... doesn't make sence if there's a 
       user-defined clustered index in the table.
    2. using a secondary index is slower than using a clustered one 
       for a table scan.

  Fixes:
    1. raise a warning.
    2. use the clustered index.
"

looks like a possible suspect. And, you know, nothing else looks even
close. So, I'd suggest to try this one first.

Regards / Mit vielen Grüssen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Developer
/_/  /_/\_, /___/\___\_\___/  MySQL GmbH, Dachauer Str. 37, D-80335 München
       <___/                  Geschäftsführer: Kaj Arnö - HRB
München 162140