mysql查询在数据库上消耗很长时间，并在其他方面正常工作

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

在我的本地机器上它工作正常并返回.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.

我搜索了任何可能的解决方案,但我没有找到,如果有人可以帮助我找到我会欣赏的原因。

2 个解决方案

#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`);

#2

Don't use LEFT unless you need it. It may inhibit optimizations.

除非您需要,否则请勿使用LEFT。它可能会抑制优化。

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.

p2c和p2s闻起来像很多:很多映射表。如果是这样,请参阅此处了解优化提示。

p needs the composite INDEX(is_family, status, date_available)

p需要复合INDEX(is_family,status,date_available)

#1