Hi Bryan, all!
Bryan Cantwell wrote:
> Is there a benefit to a combined index on a table? Or is multiple single
> column indexes better?
This is a FAQ, but I'm not aware of a place to point you for the answer.
> If I have table 'foo' with columns a, b, and c. I will have a query
> select c from foo where a in (1,2,3) and b < 12345;
> Is index on a,b better in any way than an a index and a b index?
Any multi-column index can only be used when the values for the leading
column(s) is/are known (in your example, they are).
My standard example is a phone book:
It is sorted by "last name", "first name"; you cannot use this order
when the last name in unknown (you have to sequentially scan it).
> An explain with one index sees it but doesn't use it (only the where)
> and having 2 indexes sees both and uses the one on b.
Testing select strategies requires that you have a meaningful amount of
data, and a close-to-real distribution of values:
If your tables hold too few rows, the system will notice that it is
wasteful to access them via the index, a scan is faster.
And if your value distribution differs too much from later "real" data,
the strategy selected will also differ.
> Am I right to think that 2 indexes are better than one combined one?
AFAIK, MySQL will not yet combine several indexes, but evaluate only one
per table access.
If you have a usable multi-column index, it will provide better
selectivity than a single-column index, so it is "better" if all the
leading values are given.
I cannot specifically comment on conditions using "in" and "<".
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@stripped
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603