List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:July 6 2010 6:35pm
Subject:Re: combined or single indexes?
View as plain text  
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
> like:
> 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?

"It depends":
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 "<".


HTH,
Jörg

-- 
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

Thread
combined or single indexes?Bryan Cantwell6 Jul
  • Re: combined or single indexes?Joerg Bruehe6 Jul
  • Re: combined or single indexes?Octavian Rasnita7 Jul
    • Re: combined or single indexes?Neil Tompkins8 Jul
      • Re: combined or single indexes?Johan De Meersman9 Jul
        • Re: combined or single indexes?Neil Tompkins9 Jul
          • Re: combined or single indexes?Johan De Meersman9 Jul
            • Re: combined or single indexes?mos9 Jul
              • Re: combined or single indexes?Tompkins Neil21 Jul
                • Re: combined or single indexes?MySQL)22 Jul
                  • Re: combined or single indexes?Tompkins Neil22 Jul
                    • RE: combined or single indexes?Jerry Schwartz22 Jul
                      • Re: combined or single indexes?Tompkins Neil22 Jul
                        • RE: combined or single indexes?Jerry Schwartz22 Jul
                          • Re: combined or single indexes?Neil Tompkins23 Jul