Only one index per table is used in a query.
It is probably more efficient to use the index for the where clause than the order by.
Joseph Koenig wrote:
Hi,
I'm having trouble getting the following query to use the indexes on the
order by.
SELECT vr.muzeid, vr.releaseformat, vr.releasestatus, vr.collectorsedition,
pt.title FROM v_videorelease vr, v_producttitle pt, v_prodcategory pc WHERE
pc.muzeid = vr.muzeid AND pt.muzeid = vr.muzeid AND vr.releaseformat = 'DVD'
AND vr.releasestatus = 'In Print' AND pc.categorylevel = '1' AND
pc.categoryid = '200001' ORDER BY pt.title LIMIT 0, 21
The indexes are as follows:
Table vr:
muzeid
prelrefnum
releaseformat
releasestatus
Table pt:
muzeid
titleakanum
title
subtitle
muzeid, title
The explain on the query shows that it is using a temporary table with
filesort. Can anyone help me get the ORDER BY to use the pt.title index?
Thanks,
Joe
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php