MySQL 5.5至5.7停止使用索引 [英] MySQL 5.5 to 5.7 stopped using indexes

问题描述

我有一个Magento 1.9.2.1.并成功在Apache2和MySQL 5.5上运行.

I have a Magento 1.9.2.1 . And it successfully running on Apache2 and MySQL 5.5.

我试图将其迁移到另一台服务器上，并使用NGINX和MySQL 5.7.

I have tried to migrate it to another server and used NGINX and MySQL 5.7 for it.

但是网站开始非常慢(12秒对2秒). 经过数小时的调试，我发现一个查询存在问题:

But website started to be very slow (12 seconds against 2). After several hours of debug I have found that there is a problem in one query:

SELECT
   `main_table`.`entity_id`,
   `main_table`.`name`,
   `main_table`.`path`,
   `main_table`.`is_active`,
   `main_table`.`is_anchor`,
   `url_rewrite`.`request_path`
FROM
   `catalog_category_flat_store_1` AS `main_table`
   LEFT JOIN
      `core_url_rewrite` AS `url_rewrite` ON 
      `url_rewrite`.`category_id` = `main_table`.`entity_id` AND
      `url_rewrite`.`is_system` = 1 AND
      `url_rewrite`.`store_id` = 1 AND
      `url_rewrite`.`id_path` LIKE 'category/%'
WHERE
   (`main_table`.`include_in_menu` = '1') AND
   (`main_table`.`is_active` = '1') AND
   (`main_table`.`path` like '1/2/%')
ORDER BY
   `main_table`.`position` ASC;

在解释时，我发现没有使用索引.在旧的MySQL 5.5服务器上，explain命令显示正在使用的索引.一旦我强制使用索引-新服务器将在0.01秒而不是10秒内回答.但是我认为更改magento代码的源文件不是一个好主意.

And on explain I have found that there is no index used. On the old MySQL 5.5 server explain command showing index in use. Once I force usage of index - new server answers in 0.01s instead of 10s. But I think that it is not good idea to change sourcefiles of magento code.

是否有一种方法可以强制MySQL 5.7在索引选择方面以与5.5类似的方式工作?.

Is there a way to force MySQL 5.7 work in similar way as 5.5 in index choosing?.

推荐答案

可悲的是，只有MySQL从5.7降级到5.5才解决了类似的问题. Magento sql查询没有索引

Sadly only MySQL downgrade from 5.7 to 5.5 fixed similar issue for me. Magento sql query have no index

这篇关于MySQL 5.5至5.7停止使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！