List:General Discussion« Previous MessageNext Message »
From:Grant Giddens Date:December 21 2005 5:58pm
Subject:Re: Do I need to tweak my server variables for this SELECT statement?
View as plain text  
OK guys,
  
    Here is are my tables and test SELECT queries.  Before  every query I did a FLUSH
TABLES command.  Before starting these  test, I did and ANALYIZE and OPTIMIZE on all the
tables.   Hopefully the formatting of this email isn't too bad.
  
  product table:
  
  show create table pn_pricecompare_product:
  
  CREATE TABLE `pn_pricecompare_product` (
   `prod_id` int(10) unsigned NOT NULL auto_increment,
   `asin` varchar(10) NOT NULL default '',
   `upc` varchar(14) default NULL,
   `isbn` varchar(10) default NULL,
   `ean` varchar(13) default NULL,
   `title` varchar(255) default NULL,
   `brand` varchar(64) default NULL,
   `manufacturer` varchar(64) default NULL,
   `mpn` varchar(32) default NULL,
   `model` varchar(32) default NULL,
   `artist` varchar(64) default NULL,
   `author` varchar(64) default NULL,
   `binding` varchar(64) default NULL,
   `label` varchar(64) default NULL,
   `audiencerating` varchar(64) default NULL,
   `studio` varchar(64) default NULL,
   `releasedate` date default NULL,
   `numberofpages` mediumint(8) unsigned NOT NULL default '0',
   `pubdate` date default NULL,
   `publisher` varchar(64) default NULL,
   `searchindex` varchar(32) default NULL,
   `added` date default NULL,
   `active` tinyint(1) NOT NULL default '1',
   `image_small` varchar(255) default NULL,
   `image_medium` varchar(255) default NULL,
   `image_large` varchar(255) default NULL,
   `item_height` varchar(64) default NULL,
   `item_length` varchar(64) default NULL,
   `item_weight` varchar(64) default NULL,
   `item_width` varchar(64) default NULL,
   `package_height` varchar(64) default NULL,
   `package_length` varchar(64) default NULL,
   `package_weight` varchar(64) default NULL,
   `package_width` varchar(64) default NULL,
   `list_price` decimal(12,2) unsigned default NULL,
   `lowest_price` decimal(12,2) unsigned default NULL,
   `num_merchants` smallint(6) default NULL,
   `salesrank` int(10) unsigned default NULL,
   PRIMARY KEY  (`prod_id`),
   UNIQUE KEY `asin` (`asin`),
   KEY `upc` (`upc`),
   KEY `isbn` (`isbn`),
   KEY `ean` (`ean`),
   KEY `salesrank` (`salesrank`),
   KEY `brand` (`brand`),
   KEY `manufacturer` (`manufacturer`),
   KEY `mpn` (`mpn`),
   KEY `model` (`model`),
   KEY `author` (`author`),
   KEY `artist` (`artist`),
   KEY `lowest_price` (`lowest_price`),
   KEY `added` (`added`),
   FULLTEXT KEY `title` 
(`title`,`brand`,`manufacturer`,`mpn`,`model`,`artist`,`label`,`studio`,`author`,`publisher`,`upc`,`isbn`)
  ) TYPE=MyISAM
  
  desc pn_pricecompare_product:
  Field   Type  Null  Key Default  Extra
  prod_id     int(10) unsigned            PRI     NULL      auto_increment
  asin     varchar(10)            UNI             
  upc     varchar(14)     YES      MUL     NULL      
  isbn     varchar(10)     YES      MUL     NULL      
  ean     varchar(13)     YES      MUL     NULL      
  title     varchar(255)     YES      MUL     NULL      
  brand     varchar(64)     YES      MUL     NULL      
  manufacturer     varchar(64)     YES      MUL     NULL      
  mpn     varchar(32)     YES      MUL     NULL      
  model     varchar(32)     YES      MUL     NULL      
  artist     varchar(64)     YES      MUL     NULL      
  author     varchar(64)     YES      MUL     NULL      
  binding     varchar(64)     YES            NULL       
  label     varchar(64)     YES            NULL       
  audiencerating     varchar(64)     YES            NULL       
  studio     varchar(64)     YES            NULL       
  releasedate     date     YES            NULL       
  numberofpages     mediumint(8) unsigned                   0      
  pubdate     date     YES            NULL       
  publisher     varchar(64)     YES            NULL       
  searchindex     varchar(32)     YES            NULL       
  added     date     YES     MUL     NULL      
  active     tinyint(1)                  1      
  image_small     varchar(255)     YES            NULL       
  image_medium     varchar(255)     YES            NULL       
  image_large     varchar(255)     YES            NULL       
  item_height     varchar(64)     YES            NULL       
  item_length     varchar(64)     YES            NULL       
  item_weight     varchar(64)     YES            NULL       
  item_width     varchar(64)     YES            NULL       
  package_height     varchar(64)     YES            NULL       
  package_length     varchar(64)     YES            NULL       
  package_weight     varchar(64)     YES            NULL       
  package_width     varchar(64)     YES            NULL       
  list_price     decimal(12,2) unsigned      YES           NULL       
  lowest_price     decimal(12,2) unsigned      YES     MUL     NULL       
  num_merchants     smallint(6)     YES            NULL       
  salesrank     int(10) unsigned     YES      MUL     NULL      
  
  
  
  
  
  
  show create table pn_pricecompare_catprod:
  
  CREATE TABLE `pn_pricecompare_catprod` (
   `category` int(10) unsigned NOT NULL default '0',
   `asin` char(10) NOT NULL default '',
   PRIMARY KEY  (`category`,`asin`),
   KEY `asin` (`asin`)
  ) TYPE=MyISAM
  
  desc pn_pricecompare_catprod:
  
  Field  Type  Null Key  Default  Extra
  category     int(10) unsigned            PRI     0      
  asin     char(10)            PRI             
  
  
  --------------------------------------------------------------------------------------
  
  Original Query:
  
  SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin, 
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_catprod,  pn_pricecompare_product WHERE
((pn_pricecompare_catprod.category =  '283155') AND (pn_pricecompare_catprod.asin = 
pn_pricecompare_product.asin)) ORDER BY  pn_pricecompare_product.salesrank ASC
 LIMIT 0,10  
  
  
  Execution time: over 10 minutes  (WAY too long!)
  
  
  Explanation:
   table       type        possible_keys        key       key_len        ref       rows   
    Extra
  pn_pricecompare_catprod     ref      PRIMARY,asin     PRIMARY     4      const    
369308      Using where; Using index; Using temporary; Using f...
  pn_pricecompare_product     eq_ref      asin     asin     10     
pn_pricecompare_catprod.asin     1       
  
  ------------------------------------------------
  
  Same query, without the ORDER BY or LIMIT:
  
  SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin, 
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_catprod,  pn_pricecompare_product WHERE
((pn_pricecompare_catprod.category =  '283155') AND (pn_pricecompare_catprod.asin = 
pn_pricecompare_product.asin))
  
  Execution time: 0.1674s (Pretty fast!)
  
  Explanation:
  
   table       type        possible_keys        key       key_len        ref       rows   
    Extra
  pn_pricecompare_catprod     ref      PRIMARY,asin     PRIMARY     4      const    
369308      Using where; Using index
  pn_pricecompare_product     eq_ref      asin     asin     10     
pn_pricecompare_catprod.asin     1       
  
  ------------------------------------------------
  
  Query to test the catprod table:
  
  SELECT
         pn_pricecompare_catprod.category,
         pn_pricecompare_catprod.asin
  FROM pn_pricecompare_catprod
  WHERE
         pn_pricecompare_catprod.category =  '283155'
  
  Execution time: 0.0005s  (FAST!)
  
  Explanation: 
   table       type        possible_keys        key       key_len        ref       rows   
    Extra
  pn_pricecompare_catprod     ref      PRIMARY     PRIMARY     4      const     369308    
 Using where; Using index
  
  --------------------------------------------------
  
  Query to test the product table:
  
  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 
('0684824906','140004314X','1594480001','0439784549','031615976X','0316172324','0060765313','0963679600','0743226712','0060817089')
  ORDER BY pn_pricecompare_product.salesrank ASC
  LIMIT 0,10
  
  Execution time: 0.0019s  (Fast!)
  
  Explanation:
   table       type        possible_keys        key       key_len        ref       rows   
    Extra
  pn_pricecompare_product     range      asin     asin     10      NULL     10     Using
where; Using  filesort
  
  -----------------------------------------------------
  
  Query to test the joins:
  
  SELECT count(*)
  FROM
         pn_pricecompare_catprod,
         pn_pricecompare_product
  WHERE
         pn_pricecompare_catprod.category =  '283155' AND
         pn_pricecompare_catprod.asin =  pn_pricecompare_product.asin
  
  Execution time: phpmyadmin didn't report a time, but it was pretty quick.
  
  Explanation:
  
   table       type        possible_keys        key       key_len        ref       rows   
    Extra
  pn_pricecompare_catprod     ref      PRIMARY,asin     PRIMARY     4      const    
369308      Using where; Using index
  pn_pricecompare_product     eq_ref      asin     asin     10     
pn_pricecompare_catprod.asin     1      Using index
  
  
  ----------------------------------------------------------
  
  Basically it looks like it's the ORDER BY and LIMIT clause that makes  my original query
take so long.  All the other queries were pretty  fast.  Any idea what is wrong with my
original query?
  
  Thanks for everyones help.
  
  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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1




__________________________________________________
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