List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:October 3 2010 9:29am
Subject:Re: Indexing question
View as plain text  
So if you have individual indexes for example field_1, field_2 and field_3
etc and then perform a search like

WHERE field_1 = 10
AND field_3 = 'abc'

This wouldn't improve the search ?  You have to create a index for all
possible combined field searches ?



On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey <gtowey@stripped> wrote:

> You can't use an index to select records in a range, and order them.  The
> order by will cause a filesort in that case.
>
> Additionally indexes are always read left to right.  So an index on
> ('user_id', 'product_id') will help when doing WHERE user_id=N AND
> product_id IN (1,2,3), but wouldn't help for just the condtion on
> product_id.
>
> See the manual for full details on how mysql uses indexes:
> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
>
>
> -----Original Message-----
> From: Jonas Galvez [mailto:jonasgalvez@stripped]
> Sent: Friday, October 01, 2010 11:48 AM
> To: mysql@stripped
> Subject: Indexing question
>
> Suppose I wanted to be able to perform queries against three columns of my
> table: 'user_id', 'product_id' and 'created'. Most of the time I'll just be
> range-selecting records from the table ordering by 'created'. But I may
> also
> want to select where 'user_id' = something and 'product_id' in (list, of,
> ids), ordered by 'created'. Do I need two separate indexes, one on
> 'created'
> and another on ('user_id', 'product_id', 'created'), or does having only
> the
> latter suffice the former case?
>
>
> -- Jonas, http://jonasgalvez.com.br
>
> This message contains confidential information and is intended only for the
> individual named.  If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited.  Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your
> system. E-mail transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
> 94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>

Thread
Introduction and InvitationBill Doerrfeld11 Mar
  • Indexing questionJonas Galvez1 Oct
    • RE: Indexing questionGavin Towey1 Oct
      • Re: Indexing questionTompkins Neil3 Oct
        • Re: Indexing questionJoerg Bruehe3 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
            • Re: Indexing questionJoerg Bruehe4 Oct
              • Re: Indexing questionTompkins Neil4 Oct
                • RE: Indexing questionGavin Towey4 Oct
                  • Re: Indexing questionNeil Tompkins4 Oct
                    • Re: Indexing questionTompkins Neil5 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
          • Re: Indexing questionJonas Galvez6 Oct