加速OpenCart中的MySQL查询

问题描述：

I don't know that this is a good question or not, but I have this query in one of my module:

SELECT * FROM `product` WHERE upc IN (
                        SELECT `upc` FROM `product` WHERE `upc` <> '' GROUP BY `upc` HAVING COUNT(*) > 1) 
                    ORDER BY `upc`

the product table is quite big and this query takes about 20 mins to be executed.

I am not a big MySQL expert, but it is possible to speed up this query?

My second heavy query is an UPDATE query:

UPDATE `product` SET `quantity` = '0' WHERE `sku` IN ( SELECT `id_xml_prd` FROM `ixml_prd_map` WHERE `id_xml` = '8' );

Current indexes on the product table:

And on the ixml_prd_map:

我不知道这是一个好问题，但我在我的一个模块中有这个查询： p>

  SELECT * FROM`product` WHERE upc IN（
 SELECT`upc` FROM`product` WHERE`upc`＆lt;＆gt;''GROUP BY`upcc`  HADING COUNT（*）＆gt; 1）
 ORDER BY`upc` 
  code>  pre> 
 
 产品表非常大，此查询需要大约20分钟才能执行。   p> 
 
 
我不是一个大的MySQL专家，但是可以加快这个查询的速度吗？ p> 
 
 
我的第二个重要查询是UPDATE查询： 
 
 
 更新`product` SET`quedity ='0'WHERE`sku`IN（SELECT`id_xml_prd` FROM`ixml_prd_map` WHERE`id_xml` ='8'）; \  n  code>  pre> 
 
 产品表上的当前索引： p> 
 
 
    p>  
 
 
在ixml_prd_map上： p> 
 
 
    p> 
  div>

答

You can modify your query with WHERE EXISTS like below instead of having a IN clause with a subquery.

SELECT * FROM `product` p 
WHERE EXISTS (
SELECT 1 FROM 
`product` 
WHERE `upc` <> '' AND `upc` = p.`upc`
GROUP BY `upc` 
HAVING COUNT(*) > 1) 
ORDER BY `upc`;

Also you would want to have index on upc column.

Perform a normal update join query rather than having a IN subquery

UPDATE `product` p
JOIN `ixml_prd_map` i ON p.`sku` = i.`id_xml_prd` 
AND i.`id_xml` = '8'
SET p.`quantity` = '0';

Also for this UPDATE query to be faster have an index on id_xml_prd and id_xml column ON ixml_prd_map table.

加速OpenCart中的MySQL查询

相关推荐