List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:July 8 2010 9:25pm
Subject:Re: combined or single indexes?
View as plain text  
How many indexes are recommended per table ??



On 7 Jul 2010, at 06:06, "Octavian Rasnita" <octavian.rasnita@stripped 
 > wrote:

> 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
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
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