Indexing question

--002215048fb7a8aab3049192a351
Content-Type: text/plain; charset=ISO-8859-1

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

--002215048fb7a8aab3049192a351--

RE: Indexing question

You can't use an index to select records in a range, and order them. The o=
rder 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=3DN 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@gmail.com]
Sent: Friday, October 01, 2010 11:48 AM
To: mysql@lists.mysql.com
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 als=
o
want to select where 'user_id' =3D 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 th=
e
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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg