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.tompk...@googlemail.com > wrote: > How many indexes are recommended per table ?? > > > > > On 7 Jul 2010, at 06:06, "Octavian Rasnita" < > octavian.rasn...@ssifbroker.ro> 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" < >> bcantw...@firescope.com> >> To: <mysql@lists.mysql.com> >> 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=octavian.rasn...@ssifbroker.ro >>> >>> >>> __________ 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=neil.tompk...@googlemail.com >> >> > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
