List:General Discussion« Previous MessageNext Message »
From:Grant Giddens Date:December 21 2005 2:18pm
Subject:Re: Do I need to tweak my server variables for this SELECT statement?
View as plain text  
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 
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