Hi, using LIMIT can speed up the things significantly. If you know the aproximate number of rows in your query just add LIMIT number_of_rows at the end of your query - it will look like
SELECT products.return FROM products,prodcat WHERE products.prodno=prodcat.prodno AND products.Store="0001" AND products.Class="0002" AND prodcat.category="Animal" AND prodcat.subcategory="Bear" ORDER BY products.Title LIMIT 100 Check http://www.mysql.com/doc/L/I/LIMIT_optimisation.html for further information on how MySQL deals with LIMIT Hope this helps Dobromir Velev Web Developer http://www.websitepulse.com/ -----Original Message----- From: Charley L. Tiggs <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Saturday, December 29, 2001 01:04 Subject: Newbie trying to optimizing a join search >I'm new to mysql and have gotten most things to work satisfactorily thus >far. One of the things that is stumping me at the moment is how to >perform queries using joins and how to optimize them for speed. > >the query is structured as follows: > >SELECT products.return >FROM products,prodcat >WHERE products.prodno=prodcat.prodno > AND products.Store="0001" > AND products.Class="0002" > AND prodcat.category="Animal" > AND prodcat.subcategory="Bear" >ORDER BY products.Title > >The above query takes approximately 3 seconds to complete (which is >better than the 27 seconds without indexes, i admit but still a little >slow). Is there a way to optimize this kind of search? Setup of the >tables used in this particular area are below: > >Setup: > products table with the following fields: > -ID (primary key) > -prodno > -store > -class > -title > -designer > -sugretail > -discount price > -return > > prodcat (contains categories associated with each product) with the >following fields: > -ID (primary key) > -prodno > -category > -subcategory > >All fields except for ID fields and sugretail and discount price fields >are varchar fields. Category and subcategory fields are longest at 50 >chars each. > >In products, the ID field has it's own index, product number, store, >class, designer, and title are inside an index called index_product. > >In prodcat, product number, category, and subcategory are part of an >index called index_category. > >Thanks in advance for your attention and time. > >Charley > > >--------------------------------------------------------------------- >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
