The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

This question is marked "community wiki".

asked 23 Sep '14, 03:59

Neetu's gravatar image

Neetu
1111
accept rate: 0%

1

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.

(23 Sep '14, 04:29) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19

question asked: 23 Sep '14, 03:59

question was seen: 470 times

last updated: 23 Sep '14, 04:29