Magento sql查询没有索引 [英] Magento sql query have no index

问题描述

我遇到了SQL查询问题。

I'm having a problem with SQL queries.

目前我在不同服务器上有两个magento版本，两者都从1.9.2.4升级到1.9.3.2。

Currently I have two magento versions on different servers, both are upgraded from 1.9.2.4 to 1.9.3.2.

问题是，当我用 SHOW FULL PROCESSLIST 检查查询时，我发现他们花了很多时间。经过一些研究，我试图在两台服务器上运行相同的查询（直接通过SQL命令）。

The problem, when I check queries with SHOW FULL PROCESSLIST I see that they are taking a lot of time. After some researches I have tried to run same query on both servers (directly via SQL command).

结果为1:15（68 x 263行vs 68 x 961902）在速度方面，还有一位同事检查了 EXPLAIN SQL 函数，发现在较慢的查询服务器中没有密钥（索引）。试图运行添加 USE INDEX（）并使用来自更快查询服务器的密钥，似乎这是问题，因为查询具有相同的结果和速度。

Results are 1:15 (68 x 263 rows vs 68 x 961902) in speed, also one of colleagues checked EXPLAIN SQL function and saw that in the slower query server there are no key (index). Tried to run adding USE INDEX () and using key from faster query server and seems that is the problem because query went with same results and speed.

总之，我们认为某些 MYSQL 配置不正确。

In conclusion we thing that some of MYSQL configuration are not correct.

也许有人可以建议哪个 MYSQL 配置或服务器变量会导致这个问题？
因为目前我没有想法。我试过谷歌搜索，但没有结果可以帮助我。

Maybe someone could suggest which MYSQL configuration or server variables can cause this problem? Because currently I'm out of ideas. I have tried googling but no results could help me.

SQL查询。

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/3/%')
      AND  (`level` <= 3)
    ORDER BY  `main_table`.`position` ASC

推荐答案

可悲的是只有MySQL从5.7降级到5.5固定这个问题。

Sadly only MySQL downgrade from 5.7 to 5.5 fixed this issue.

这篇关于Magento sql查询没有索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！