mysql query consumes long time on database and works fine on other

Question

I am executing this query to get the number of products for each category on my website

SELECT  COUNT(DISTINCT p.product_id) AS total
    FROM  category_path cp
    LEFT JOIN  product_to_category p2c  ON (cp.category_id = p2c.category_id)
    LEFT JOIN  product p  ON (p2c.product_id = p.product_id)
    LEFT JOIN  product_description pd  ON (p.product_id = pd.product_id)
    LEFT JOIN  product_to_store p2s  ON (p.product_id = p2s.product_id)
    WHERE  pd.language_id = '1'
      AND  p.status = '1'
      AND  p.date_available <= NOW()
      AND  p2s.store_id = '0'
      AND  cp.path_id = '20'
      AND  p.is_family ='1';

On my local machine it works fine and returns the result within .02

+-------+
| total |
+-------+
|   392 |
+-------+
1 row in set (0.02 sec)`

but when I execute the same query on my server's DB it takes a lot

+-------+
| total |
+-------+
|   412 |
+-------+
1 row in set (0.78 sec)

I searched for any possible solution but I didn't find, If anyone can help me to find the reason I will appreciate that.

Sithara Galagedara · Accepted Answer · 2017-05-19 12:36:23Z

1

You can try with Indexing the Columns

Example -:

ALTER TABLE `table` ADD INDEX `product_id` (`product_id`);
ALTER TABLE `table` ADD INDEX `language_id` (`language_id`);
ALTER TABLE `table` ADD INDEX `status` (`status`);
ALTER TABLE `table` ADD INDEX `date_available` (`date_available`);
ALTER TABLE `table` ADD INDEX `store_id` (`store_id`);
ALTER TABLE `table` ADD INDEX `path_id` (`path_id`);
ALTER TABLE `table` ADD INDEX `is_family` (`is_family`);

answered May 19, 2017 at 12:36

Sithara Galagedara

613 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rick James Over a year ago

When appropriate, 'composite' indexes are better than single-column indexes.

Rick James Over a year ago

Do not index "flag" by themselves. They are rarely useful.

Rick James · Accepted Answer · 2017-05-19 18:18:25Z

1

Don't use LEFT unless you need it. It may inhibit optimizations.
Are you using the same version on both machines?
p2c and p2s smell like a many:many mapping tables. If so, see this for tips on optimizing.
p needs the composite INDEX(is_family, status, date_available)

answered May 19, 2017 at 18:18

Rick James

144k15 gold badges144 silver badges255 bronze badges

2 Comments

odai Over a year ago

thank you for your notes, but since I am using the same version, how the execution time for the same query will vary in this way, it was taking around 20 sec to load any page but after I indexed the important columns it worked fine?

Rick James Over a year ago

Similar number of rows in the tables? Same value of innodb_buffer_pool_size?

Collectives™ on Stack Overflow

mysql query consumes long time on database and works fine on other

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related