on 2014 Sep 23 4:59 AM
Problem: Below query is slow query, minimum execution time is 3.x sec and as the category id increase in "IN" clause execution of query taking more that a minute.
QUERY:
explain SELECT a.* FROM label a INNER JOIN category_label c ON a.id = c.label_id INNER JOIN product_label p ON a.id = p.label_id INNER JOIN product p2 ON p.product_id = p2.id INNER JOIN category c2 ON p2.category_id = c2.id INNER JOIN category c3 ON (c2.lft BETWEEN c3.lft AND c3.rgt) INNER JOIN user u ON ((u.id = p2.user_id AND u.is_active = 1)) INNER JOIN country c4 ON (p2.country_id = c4.id) WHERE (c.category_id IN ('843', '848', '849', '853', '856', '858') AND a.is_filterable = 1 AND a.type <> "textarea" AND c2.rgt = (c2.lft + 1) AND c3.id IN ('843', '848', '849', '853', '856', '858') AND c4.id IN ('190') AND p2.status = 1) GROUP BY a.id ORDER BY a.sort_order
query explanation - ****** 1. row *** id: 1 select_type: SIMPLE table: c4 type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: c3 type: range possible_keys: PRIMARY,lft_rgt_inx key: PRIMARY key_len: 4 ref: NULL rows: 6 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: c type: range possible_keys: PRIMARY,label_id,category_id key: PRIMARY key_len: 4 ref: NULL rows: 197 Extra: Using where; Using index; Using join buffer *** 4. row ****** id: 1 select_type: SIMPLE table: a type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: c.label_id rows: 1 Extra: Using where
****** 5. row *** id: 1 select_type: SIMPLE table: p type: ref possible_keys: product_id,label_id key: label_id key_len: 4 ref: c.label_id rows: 3827 Extra: *** 6. row *** id: 1 select_type: SIMPLE table: p2 type: eq_ref possible_keys: PRIMARY,category_id,user_id,country_id key: PRIMARY key_len: 8 ref: p.product_id rows: 1 Extra: Using where *** 7. row *** id: 1 select_type: SIMPLE table: c2 type: eq_ref possible_keys: PRIMARY,lft_rgt_inx key: PRIMARY key_len: 4 ref: p2.category_id rows: 1 Extra: Using where *** 8. row ****** id: 1 select_type: SIMPLE table: u type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: p2.user_id rows: 1 Extra: Using where
Show create table -
labelMaster
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL,
type
varchar(50) NOT NULL COMMENT 'textbox, checkbox, selectbox, textarea',
show_filter
tinyint(1) NOT NULL DEFAULT '1',
select_all_level
int(11) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB
CREATE TABLE categoryMaster
(
id
int(11) NOT NULL AUTO_INCREMENT,
parent_id
int(11) NOT NULL DEFAULT '0',
lft
int(11) NOT NULL DEFAULT '0',
rgt
int(11) NOT NULL DEFAULT '0',
level
tinyint(4) NOT NULL DEFAULT '0',
product_count
int(11) NOT NULL,
PRIMARY KEY (id
),
UNIQUE KEY lft_rgt_inx
(lft
,rgt
),
KEY parent_id
(parent_id
)
) ENGINE=InnoDB
CREATE TABLE productMaster
(
id
bigint(20) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL,
category_id
int(11) NOT NULL,
status
tinyint(1) NOT NULL DEFAULT '-1' ,
user_id
int(11) NOT NULL,
label_value_ids
varchar (255),
product_source_url
varchar(255) NOT NULL,
country_id
int(4) NOT NULL,
state_id
int(5) NOT NULL,
PRIMARY KEY (id
),
KEY category_id
(category_id
),
KEY user_id
(user_id
),
KEY x_area_id
(x_area_id
),
KEY state_id
(state_id
),
KEY country_id
(country_id
),
FULLTEXT KEY name
(name
),
FULLTEXT KEY label_value_ids
(label_value_ids
)
) ENGINE=MyISAM
CREATE TABLE product_label
(
id
bigint(20) NOT NULL AUTO_INCREMENT,
product_id
bigint(20) NOT NULL,
label_id
int(11) NOT NULL,
label_value
varchar(1200) DEFAULT NULL,
category_id
int(11) NOT NULL,
PRIMARY KEY (id
),
KEY product_id
(product_id
),
KEY category_id
(category_id
),
KEY label_id
(label_id
)
) ENGINE=InnoDB
CREATE TABLE label_values
(
id
int(11) NOT NULL AUTO_INCREMENT,
label_id
int(11) NOT NULL,
value
varchar(255) NOT NULL,
sort_order
smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (id
),
KEY label_id
(label_id
),
CONSTRAINT label_values_ibfk_1
FOREIGN KEY (label_id
) REFERENCES label
(id
) ON DELETE CASCADE
) ENGINE=InnoDB
CREATE TABLE category_label
(
category_id
int(11) NOT NULL,
label_id
int(11) NOT NULL,
is_listview
tinyint(1) NOT NULL,
sort_order
smallint(2) NOT NULL,
PRIMARY KEY (category_id
,label_id
),
KEY label_id
(label_id
),
KEY category_id
(category_id
)
) ENGINE=MyISAM
Please suggest me how can i rewrite the qurey to reduce execution time and make website fast.
also I have tried to change database engine.
Please note: The query already using cache.
Request clarification before answering.
User | Count |
---|---|
61 | |
8 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.