Adding UNIQUE index doesn"t update key stats; 4.0.12

4.0.12 (and 3.23.55), MyISAM tables, Linux on Intel, MySQL binary or
self-compiled.

Maybe this isn't a bug, but it's rather confusing and felt like a
serious bug before we tracked it down:

When adding a unique compound index to a table, the key-distribution
stats aren't updated, so you have to do an ANALYZE TABLE before the
query optimizer can make good use of the index.

The stats _are_ updated when adding a non-unique, but otherwise
identical, compound key.

I have a test case that shows an EXPLAIN on a simple query guessing
well, vs. guessing poorly, after creating a normal vs. unique index.
An 11k tarfile containing the relevant files is at:

http://renoir.artselect.com/~harlan/unique.tar.gz

The results of running "mysql test
------------------------------------------------------------ ---
describe a:

Field Type Null Key Default Extra
aID int(11) PRI NULL auto_increment


describe a_b:

Field Type Null Key Default Extra
aID int(11) 0
bID int(11) 0
seq int(11) 0


Explain with no indexes:

table type possible_keys key key_len ref rows Extra
a_b ALL NULL NULL NULL NULL 1993
a eq_ref PRIMARY PRIMARY 4 a_b.aID 1 Using where; Using index


Add NON-unique compound index on a_b (aID, bID, seq); resulting
EXPLAIN shows improvement over no index:

table type possible_keys key key_len ref rows Extra
a index PRIMARY PRIMARY 4 NULL 980 Using index
a_b ref aID aID 8 a.aID,a.aID 1 Using index


Drop the non-unique index and add a UNIQUE index instead, and the
EXPLAIN looks as bad as with no index:

table type possible_keys key key_len ref rows Extra
a_b index aID aID 12 NULL 1993 Using index
a eq_ref PRIMARY PRIMARY 4 a_b.aID 1 Using where; Using index


Do an 'ANALYZE TABLE a_b', and now the join looks the same as after
adding non-unique index:

table type possible_keys key key_len ref rows Extra
a index PRIMARY PRIMARY 4 NULL 980 Using index
a_b ref aID aID 8 a.aID,a.aID 1 Using index

------------------------------------------------------------ -----

In the real-world scenario in which this arose, the differences and
consequences were much more dramatic than in this small test case.

I hope this is of some help. Many people wouldn't expect creation of
a unique index to perform worse than creation of a non-unique index,
but if they don't analyze their tables after creating every index
that's what they might find.

Thanks,

--Pete

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Adding UNIQUE index doesn"t update key stats; 4.0.12

Hi!

On Apr 11, Pete Harlan wrote:
> 4.0.12 (and 3.23.55), MyISAM tables, Linux on Intel, MySQL binary or
> self-compiled.
>
> Maybe this isn't a bug, but it's rather confusing and felt like a
> serious bug before we tracked it down:

It's not a bug.

> When adding a unique compound index to a table, the key-distribution
> stats aren't updated, so you have to do an ANALYZE TABLE before the
> query optimizer can make good use of the index.
>
> The stats _are_ updated when adding a non-unique, but otherwise
> identical, compound key.

The fact that ALTER TABLE performs ANALYZE internally is not a
documented ALTER TABLE feature, it's a by-product of ADD INDEX
optimization - that is "repair_by_sorting", that you can see in
SHOW PROCESSLIST. First copy of table is created without an index and
then internal repair is used to recreate an index.
For UNIQUE indexes MySQL does not uses repair, because if table happens
to have duplicated values in this key, it's better to catch them early,
not after all the table data are copied.

As manual does NOT specify that ALTER TABLE updates key distribution,
it does not have to.

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Adding UNIQUE index doesn"t update key stats; 4.0.12

Thank you for looking into this! And thank you for your explanation
of what's happening.

I suppose it's difficult to use the knowlege gained during the
creation of the index to initialize the key-distribution stats, or
someone would have done that already.

It was confusing to us, but I see it's not a bug per se. If anyone
else runs into this confusion on mysql-user, I'll enlighten them.

--Pete


On Tue, Apr 22, 2003 at 08:55:49PM +0400, Sergei Golubchik wrote:
> Hi!
>
> On Apr 11, Pete Harlan wrote:
> > 4.0.12 (and 3.23.55), MyISAM tables, Linux on Intel, MySQL binary or
> > self-compiled.
> >
> > Maybe this isn't a bug, but it's rather confusing and felt like a
> > serious bug before we tracked it down:
>
> It's not a bug.
>
> > When adding a unique compound index to a table, the key-distribution
> > stats aren't updated, so you have to do an ANALYZE TABLE before the
> > query optimizer can make good use of the index.
> >
> > The stats _are_ updated when adding a non-unique, but otherwise
> > identical, compound key.
>
> The fact that ALTER TABLE performs ANALYZE internally is not a
> documented ALTER TABLE feature, it's a by-product of ADD INDEX
> optimization - that is "repair_by_sorting", that you can see in
> SHOW PROCESSLIST. First copy of table is created without an index and
> then internal repair is used to recreate an index.
> For UNIQUE indexes MySQL does not uses repair, because if table happens
> to have duplicated values in this key, it's better to catch them early,
> not after all the table data are copied.
>
> As manual does NOT specify that ALTER TABLE updates key distribution,
> it does not have to.
>
> Regards,
> Sergei
>
> --
> MySQL Development Team
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/
>
>

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org