List:General Discussion« Previous MessageNext Message »
From:Hank Date:December 21 2005 2:10pm
Subject:Re: Do I need to tweak my server variables for this SELECT statement?
View as plain text  
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
Thread
Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
      • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
        • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
            • Re: Do I need to tweak my server variables for this SELECT statement?Hank23 Dec
              • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard23 Dec
                  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                    • Re: Do I need to tweak my server variables for this SELECT statement?Hank24 Dec
    • Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinPeter Brawley21 Dec
      • Re: Filtering joinGleb Paharenko21 Dec
    • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinSGreen21 Dec
        • Re: Filtering joinEris Ristemena21 Dec
          • Re: Filtering joinSGreen21 Dec
            • Re: Filtering joinEris Ristemena21 Dec
Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?SGreen21 Dec