From: Hank
Date: December 21 2005 2:10pm
Subject: Re: Do I need to tweak my server variables for this SELECT statement?
List-Archive: http://lists.mysql.com/mysql/193097
Message-Id: <1990a96b0512210610g66a6a7f1kb8326f8b8bb956f7@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 =3D  '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 =3D  '283155' AND
       pn_pricecompare_catprod.asin =3D  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