You set up your situation very well but for one small item. Please allow me to kindly introduce you to the EXPLAIN command....
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Post the results from using that on your query and we will be good to go :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Chris W. Parker" <[EMAIL PROTECTED]> wrote on 10/07/2004 03:08:47 PM: > hello, > > i was wondering if anyone could help me to optimize a query i use when > gathering search results? this is easily the most complicated query i've > written (and likely a walk in the park for most of you) and because of > this, i'm afraid it's a bit slow. > > SELECT COUNT(p.id) > FROM products AS p > LEFT JOIN products_categories AS pc > ON pc.prodid = p.id > OR pc.prod_sequential_id = p.sequential_id > LEFT JOIN products_masids AS pmas > ON pmas.prodid = p.id > OR pmas.prod_sequential_id = p.sequential_id > LEFT JOIN products_media AS pmed > ON (pmed.prodid = p.id > OR pmed.prod_sequential_id = p.sequential_id) > AND pmed.type = 0 > WHERE > pc.plft >= 17 > AND pc.prgt <= 174 > AND p.is_active = 1 > GROUP BY p.id > > the above query is the first query i initially run to get a count of > 'total products found'. the purpose being so that i can determine the > number of pages to display. > > i am using 'modified preorder tree traversal' > (http://www.sitepoint.com/article/hierarchical-data-database/2) for > classifying my products into categories. > > also, my mysql version is 3.23.54. > > i've pretty much learned all i know about databases by messing around > with them and using the bits and pieces i read on the internet. in other > words, i don't know what important information i may or may not be > leaving out of this post. please let me know what else is required for > an evaluation of my query to be made, if indeed something is needed. > > oh... what i *could* say is that the hardware i'm using for this box is > not too shabby (not awesome either) and for this exact query it returns > 586 records in 2.03 seconds. i have 733 products total in my database. > > > thank you, > chris. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
