List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:July 9 2010 5:30am
Subject:Re: combined or single indexes?
View as plain text  
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 <vegivamp@stripped> 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 <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