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 -
CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE 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.
This question is marked "community wiki".
|
Is that a question on a MySQL database?
If so, please ask that on an appropriate forum, this one is for SAP Sybase SQL Anywhere, cf. the FAQ.