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
