Howdy all,

I noticed something strange today when trying to speed up some queries
and can't figure out if it's a bug in MySQL or something I don't
underand about indices.

The table I'm dealing with has 13 columns and all are being used in
various combinations for SELECT queries. To speed up some of the more
complicated queries, i've added 6 multi-column indices. We'll call
them Index1 - Index6, for simplicity.

A new query was running slowly, so I ran it through "explain" and
discovered that it was using Index6. I added a new index, Index7 that
I thought would be a better fit. After re-running explain, it's now
opting for Index1. I don't understand how adding a new index could
cause the system to choose a different pre-existing index. Any
thoughts?

If it helps, the system is running MySQL 5.0.45.

Thanks,

Blake

Blake <blake (AT) custom-fitz-software (DOT) com> wrote:
Adding another index will also update statistics for existing indexes.
(this is what ANALYZE TABLE does). Optimizer decision depends on
existing indexes *and* statistical information for such indexes.

If you receive a bad (and seemingly inappropriate) result from EXPLAIN
you should always run ANALYZE TABLE first. Ideally you should run
ANALYZE TABLE regularly (for tables that are constantly modified) or
after changing significant part of a table.


XL

On Tue, 5 Oct 2010 07:47:41 +0200, Axel Schwenke
<axel.schwenke (AT) gmx (DOT) de> wrote:


Thanks, that fixed the problem. I was always under the impression
that index stats were updated on INSERT / DELETE / UPDATE queries, and
this was what caused those queries to take longer when using multiple
indices - didn't even know ANALYZE TABLE existsed.

Cheers

On Tue, 05 Oct 2010 12:09:32 -0500, Blake wrote:
The *indexes* are updated, but the stats about them aren't, on a
row-by-row basis. Collecting the stats is a comparatively expensive
process, and on large stable-ish tables with an even distribution of
values in the indexed column, the stats won't change a whole lot over
time anyway.

--
76. If the hero runs up to my roof, I will not run up after him and
struggle with him in an attempt to push him over the edge. I will
also not engage him at the edge of a cliff. (In the middle of a
rope-bridge over a river of lava is not even worth considering.)