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
>
