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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
