Thank Hank. I will try this. When you say the table descriptions, do you mean for me to
post my CREATE TABLE syntax of how I created the table?
Thanks,
Grant
Hank <heskin@stripped> wrote: I don't think the problem is going to be solved with
the my.cnf file.
Here's what I would try..
1- run and time the original query without the ORDER BY or LIMIT clauses
2- run and time the following breakdown queries, to see if the
indexes are at least working correctly:
-- test catprod
SELECT
pn_pricecompare_catprod.category,
pn_pricecompare_catprod.asin
FROM pn_pricecompare_catprod
WHERE
pn_pricecompare_catprod.category = '283155'
-- test product
SELECT
pn_pricecompare_product.title,
pn_pricecompare_product.prod_id,
pn_pricecompare_product.image_small,
pn_pricecompare_product.brand,
pn_pricecompare_product.manufacturer,
pn_pricecompare_product.mpn,
pn_pricecompare_product.model,
pn_pricecompare_product.artist,
pn_pricecompare_product.author,
pn_pricecompare_product.binding,
pn_pricecompare_product.label,
pn_pricecompare_product.audiencerating,
pn_pricecompare_product.studio,
pn_pricecompare_product.releasedate,
pn_pricecompare_product.numberofpages,
pn_pricecompare_product.pubdate,
pn_pricecompare_product.publisher,
pn_pricecompare_product.searchindex,
pn_pricecompare_product.lowest_price,
pn_pricecompare_product.num_merchants
FROM pn_pricecompare_product
WHERE
pn_pricecompare_product.asin IN ()
ORDER BY pn_pricecompare_product.salesrank ASC
LIMIT 0,10
-- test just getting a count of the join result
SELECT count(*)
FROM
pn_pricecompare_catprod,
pn_pricecompare_product
WHERE
pn_pricecompare_catprod.category = '283155' AND
pn_pricecompare_catprod.asin = pn_pricecompare_product.asin
The results of those queries should shed some light on where the
problem is being introduced.
Also, table descriptions of both tables would be helpful in locating
the problem.
-Hank
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
