You may also want to try putting the joined table's filters in the ON condition of the LEFT JOIN, so that the filter is applied *before* the join, not after:
SELECT DISTINCT
xno_items.itemid, prod_ref, title, short_title, subtitle, promo, author, maincat, inventory, price, strikeprice, retprice, reteprice, saleperc, onsale, clearance, blockp, saledt, xno_items.added, updated, thumb_image, xno_manufacturers.mfg_name
FROM xno_items
LEFT JOIN xno_prod_index
ON xno_items.itemid = xno_prod_index.itemid
AND xno_prod_index.word ='thermometer' # Note it moved from WHERE to here...
LEFT JOIN xno_item_cat
ON xno_items.itemid = xno_item_cat.itemid
LEFT JOIN xno_categories
ON xno_item_cat.catid = xno_categories.catid
AND xno_categories.active = 1 # Note it moved from WHERE to here...
LEFT JOIN xno_manufacturers
ON xno_items.mfgid = xno_manufacturers.mfgid
WHERE xno_items.active = 1
ORDER BY
xno_prod_index.quan DESC
, xno_prod_index.density DESC
, xno_items.ordered DESC
, xno_items.title;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com