From: Neil Tompkins Date: July 9 2010 5:30am Subject: Re: combined or single indexes? List-Archive: http://lists.mysql.com/mysql/222128 Message-Id: <5A1B6FC9-31A0-4A03-8050-D1A128E8C9A3@googlemail.com> MIME-Version: 1.0 (iPod Mail 7E18) Content-Type: multipart/alternative; boundary=Apple-Mail-1--301450002 Content-Transfer-Encoding: 7bit --Apple-Mail-1--301450002 Content-Type: text/plain; charset=us-ascii; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Should we be looking to create a index for all fields that we might be perform a select condition in a where clause for ? On 9 Jul 2010, at 05:59, Johan De Meersman wrote: > 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 > wrote: > How many indexes are recommended per table ?? > > > > > On 7 Jul 2010, at 06:06, "Octavian Rasnita" > 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" > > To: > 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.rasnita@stripped > > > __________ 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.tompkins@stripped > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@stripped > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel --Apple-Mail-1--301450002--