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
