List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:July 9 2010 4:59am
Subject:Re: combined or single indexes?
View as plain text  
As many as you need, but no more :-)

The right indexes give you a boost in select performance, but every index
also needs to be updated when your data changes.



On Thu, Jul 8, 2010 at 11:25 PM, Neil Tompkins <neil.tompkins@stripped
> wrote:

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


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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