We have this opencart installation with a database size of roughly 120 MB, 90MB of which is MyISAM and other is InnoDB.
Most of the queries like :
SELECT DISTINCT *, a.*, vd.seo, p.image, p2s.price, p.product_id,
p.image, pd.name,
( SELECT price
FROM product_special ps
WHERE ps.product_id = p.product_id
AND ps.customer_group_id = '1'
AND ((ps.date_start = '0000-00-00'
OR ps.date_start < NOW())
AND (ps.date_end = '0000-00-00'
OR ps.date_end > NOW()
)
AND ps.store_id = '0'
)
ORDER BY ps.priority ASC, ps.price ASC
LIMIT 1
) AS special
FROM product_variation a, variation_description vd, product_to_category pc,
variation v, product_description pd, product_to_store_alter p2s,
product p
WHERE v.variation_group_id='18'
AND p2s.product_id = p.product_id
AND p2s.active = '1'
AND p2s.store_id = '0'
AND pc.product_id = p.product_id
AND a.variation_id = v.variation_id
AND vd.variation_id = v.variation_id
AND vd.variation_id = a.variation_id
AND v.variation_id = '1418'
AND p.product_id = pd.product_id
AND a.product_id = pd.product_id
AND p2s.quantity > '0'
AND p.product_id != '17230'
AND p.status = '1'
AND p.date_available <= NOW()
GROUP BY p.product_id
ORDER BY a.product_id DESC
LIMIT 8;
The above query takes around 2 secs to execute. MySQL Server 5.5, key_buffer is 256MB and innodb_buffer_pool has 128M.
Sort and join buffers have 8M each.
Anything where I can improve, cause such queries run multiple times for a single page. Help would be appreciated!
UPDATE :
Explain statement
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY v const PRIMARY,variation_group_id PRIMARY 8 const,const 1 Using temporary; Using filesort
1 PRIMARY vd ref PRIMARY PRIMARY 4 const 1 Using where
1 PRIMARY p ref PRIMARY,status status 1 const 13711 Using where
1 PRIMARY pd ref PRIMARY PRIMARY 4 ninec.p.product_id 1
1 PRIMARY p2s eq_ref PRIMARY PRIMARY 8 ninec.p.product_id,const 1 Using where
1 PRIMARY a ref product_id,variation_id product_id 4 ninec.p2s.product_id 14 Using where
1 PRIMARY pc ref product_id product_id 4 ninec.p.product_id 3 Using index
2 DEPENDENT SUBQUERY ps ref customer_group_id,store_id,product_id customer_group_id 4 const 1 Using where; Using filesort
UPDATE
Adding CREATE TABLE
statements for above tables :
CREATE TABLE variation (
variation_id int(11) NOT NULL AUTO_INCREMENT,
variation_group_id int(11) NOT NULL,
sort_order int(3) NOT NULL,
PRIMARY KEY (variation_id),
KEY variation_group_id (variation_group_id)
) ENGINE=MyISAM
CREATE TABLE variation_description (
variation_id int(11) NOT NULL,
language_id int(11) NOT NULL,
name varchar(64) COLLATE utf8_bin NOT NULL,
description varchar(255) COLLATE utf8_bin NOT NULL,
seo varchar(255) COLLATE utf8_bin NOT NULL,
image varchar(255) COLLATE utf8_bin NOT NULL,
is_moq tinyint(1) NOT NULL,
url_link varchar(255) COLLATE utf8_bin NOT NULL,
video text COLLATE utf8_bin NOT NULL,
download_link varchar(255) COLLATE utf8_bin NOT NULL,
launch_date date NOT NULL,
PRIMARY KEY (variation_id,language_id),
KEY seo (seo),
KEY language_id (language_id)
) ENGINE=MyISAM
CREATE TABLE product (
product_id int(11) NOT NULL AUTO_INCREMENT,
model varchar(64) COLLATE utf8_bin NOT NULL,
sku varchar(64) COLLATE utf8_bin NOT NULL,
upc varchar(12) COLLATE utf8_bin NOT NULL,
location varchar(128) COLLATE utf8_bin NOT NULL,
quantity int(4) NOT NULL DEFAULT '0',
rs_quantity int(4) NOT NULL,
moq_quantity int(4) NOT NULL,
stock_status_id int(11) NOT NULL,
image varchar(255) COLLATE utf8_bin DEFAULT NULL,
watermark_pos int(1) NOT NULL,
watermark_colour int(1) NOT NULL,
manufacturer_id int(11) NOT NULL,
vendor_id varchar(25) COLLATE utf8_bin NOT NULL,
shipping tinyint(1) NOT NULL DEFAULT '1',
mrp_price decimal(15,2) NOT NULL,
price decimal(15,4) NOT NULL DEFAULT '0.0000',
org_price decimal(15,0) NOT NULL,
moq_price decimal(15,0) NOT NULL,
points int(8) NOT NULL DEFAULT '0',
tax_class_id int(11) NOT NULL,
date_available date NOT NULL,
weight decimal(15,8) NOT NULL DEFAULT '0.00000000',
weight_class_id int(11) NOT NULL DEFAULT '0',
length decimal(15,8) NOT NULL DEFAULT '0.00000000',
width decimal(15,8) NOT NULL DEFAULT '0.00000000',
height decimal(15,8) NOT NULL DEFAULT '0.00000000',
length_total float NOT NULL,
length_class_id int(11) NOT NULL DEFAULT '0',
subtract tinyint(1) NOT NULL DEFAULT '1',
minimum int(11) NOT NULL DEFAULT '1',
sort_order int(11) NOT NULL DEFAULT '0',
best_seller int(1) NOT NULL,
new_arrival int(1) NOT NULL,
readytoship int(1) NOT NULL,
exclusive int(1) NOT NULL,
sale int(1) NOT NULL,
is_readymade tinyint(1) NOT NULL,
status tinyint(1) NOT NULL DEFAULT '0',
date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
date_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
viewed int(5) NOT NULL DEFAULT '0',
time_to_ship varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '5',
wash_care varchar(255) COLLATE utf8_bin NOT NULL,
measure_group_id int(11) NOT NULL,
wishlist int(11) NOT NULL,
shoppingcart int(11) NOT NULL,
barcode varchar(99) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (product_id),
KEY sort_order (sort_order),
KEY best_seller (best_seller),
KEY new_arrival (new_arrival),
KEY readytoship (readytoship),
KEY exclusive (exclusive),
KEY sale (sale),
KEY status (status),
KEY viewed (viewed),
KEY time_to_ship (time_to_ship),
KEY model (model)
) ENGINE=InnoDB
CREATE TABLE product_description (
product_id int(11) NOT NULL AUTO_INCREMENT,
language_id int(11) NOT NULL,
name varchar(255) COLLATE utf8_bin NOT NULL,
description text COLLATE utf8_bin NOT NULL,
meta_description varchar(255) COLLATE utf8_bin NOT NULL,
meta_keyword varchar(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (product_id,language_id),
KEY name (name),
KEY language_id (language_id)
) ENGINE=MyISAM
CREATE TABLE product_special (
product_special_id int(11) NOT NULL AUTO_INCREMENT,
product_id int(11) NOT NULL,
customer_group_id int(11) NOT NULL,
priority int(5) NOT NULL DEFAULT '1',
price decimal(15,4) NOT NULL DEFAULT '0.0000',
date_start date NOT NULL DEFAULT '0000-00-00',
date_end date NOT NULL DEFAULT '0000-00-00',
store_id int(11) NOT NULL,
PRIMARY KEY (product_special_id),
KEY product_id (product_id),
KEY customer_group_id (customer_group_id),
KEY store_id (store_id)
) ENGINE=MyISAM
CREATE TABLE product_to_category (
product_id int(11) NOT NULL,
category_id int(11) NOT NULL,
KEY product_id (product_id,category_id),
KEY category_id (category_id,product_id)
) ENGINE=InnoDB
CREATE TABLE product_to_store_alter (
product_id int(11) NOT NULL,
store_id int(11) NOT NULL DEFAULT '0',
location varchar(128) NOT NULL,
quantity int(10) NOT NULL DEFAULT '0',
price decimal(15,4) NOT NULL DEFAULT '0.0000',
substract tinyint(1) NOT NULL DEFAULT '1',
minimum int(11) NOT NULL DEFAULT '1',
active tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (product_id,store_id)
) ENGINE=MyISAM
CREATE TABLE product_variation (
product_id int(11) NOT NULL,
variation_id varchar(255) COLLATE utf8_bin NOT NULL,
language_id int(11) NOT NULL,
KEY product_id (product_id),
KEY variation_id (variation_id)
) ENGINE=MyISAM
Best Answer
Use the
JOIN ... ON ...
syntax, please.INDEX(customer_group_id, product_id, store_id)
will be a better index for ps.Don't introduce the dependent subquery until you have a
DISTINCT
list ofproduct_ids
. That way you won't be evaluating it repeatedly. That is,use the remaining query as a subquery:
SELECT *, ( SELECT price ... ) AS special FROM ( SELECT DISTINCT ... ORDER BY ... LIMIT 8 ) AS x;
Now, you will be executing the subquery only 8 times, not upwards of a million times.
Probably other tables could be delayed in this fashion; I don't have the patience to wade through that 7-table
JOIN
. Probablypc
anda
.Be consistent about which
product_id
you are using inGROUP BY
andORDER BY
; they seem to be all equal, and probably the optimizer is smart enough. Suggest usingp.
since that is the first in theEXPLAIN
.Make one of the
KEYs
inproduct_to_category
thePRIMARY KEY
. (I assume you don't want dups?)Don't bother indexing flags (sale, viewed, ...)