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 (<some test asins>)
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
