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:[email protected]]
Sent: Friday, October 01, 2010 11:48 AM
To: [email protected]
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/[email protected]
