List:General Discussion« Previous MessageNext Message »
From:Octavian Rasnita Date:July 7 2010 5:06am
Subject:Re: combined or single indexes?
View as plain text  
Hi,

MySQL can use a single index in a query as you've seen in the result of 
explain.
Of course it is better to have an index made of 2 or more columns because it 
will match better the query.

But if I remember well, the in() function  can't use an index.
And I think it also can't use an index if you use OR operators like:

select foo from table where a=1 or a=2;

So for your query the single-column index for the second column is enough.

I've seen some tricks for using a faster method by using union and 2-column 
index, something like:

select foo from table where a=1 and b<1234
union
select foo from table where a=2 and b<1234
union
select foo from table where a=3 and b<1234

This might be faster in some cases because the query would be able to use 
the 2-column index, and especially if the content of those columns is made 
only of numbers, because in that case the query will use only the index, 
without getting data from the table.

--
Octavian

----- Original Message ----- 
From: "Bryan Cantwell" <bcantwell@stripped>
To: <mysql@stripped>
Sent: Tuesday, July 06, 2010 6:41 PM
Subject: combined or single indexes?


> Is there a benefit to a combined index on a table? Or is multiple single
> column indexes better?
>
> 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?
> 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.
>
> Am I right to think that 2 indexes are better than one combined one?
>
> thx,
> Bryancan
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus 
> signature database 5257 (20100707) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> 


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5257
(20100707) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



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