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]
