What version of MySQL are you currently running? There exist a feature in release 3.23.x where a filesort will be used to perform GROUP BY and ORDER BY's actions when the key part is not used in the where clause. If I am correct, the optimizer has been tweaked in version 4.x. Forgive me if I am off base with this information.
I hope this helps. Victor Pendleton -----Original Message----- From: Joseph Koenig To: gerald_clark Cc: [EMAIL PROTECTED] Sent: 10/23/02 2:29 PM Subject: Re: Optimizing Query to use Index in ORDER BY The explain shows that it is using the muzeid key for the pt table, so yes it is using it for the join. The query takes 1.46 seconds with the GROUP BY and 0.01 without. I was hoping to find a way to speed with query up. Thanks, Joe > From: gerald_clark <[EMAIL PROTECTED]> > Date: Wed, 23 Oct 2002 14:00:29 -0500 > To: Joseph Koenig <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: Optimizing Query to use Index in ORDER BY > > Does explain say an index is used on pt for the join? > 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 > --------------------------------------------------------------------- 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
