How stable is version 4.x? This database will be moved into production fairly shortly and I need to make sure the version of mysql is extremely stable. Thanks,
Joe > From: Victor Pendleton <[EMAIL PROTECTED]> > Date: Wed, 23 Oct 2002 14:40:58 -0500 > To: "'Joseph Koenig '" <[EMAIL PROTECTED]>, "'gerald_clark '" > <[EMAIL PROTECTED]> > Cc: "'[EMAIL PROTECTED] '" <[EMAIL PROTECTED]> > Subject: RE: Optimizing Query to use Index in ORDER BY > > 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
