Magento sql query has no index
I have a problem with SQL queries.
I currently have two versions of magento on different servers, both upgraded from 1.9.2.4 to 1.9.3.2.
The problem is when I check the requests with help SHOW FULL PROCESSLIST
I see that they are taking a long time. After some research, I tried to execute the same query on both servers (directly via the SQL command).
The results are 1:15 (68 x 263 rows versus 68 x 961902) in speed, and a colleague also checked the function EXPLAIN SQL
and saw that there was no key (index) on the slower query server. Tried to run the add USE INDEX ()
and use the key from a faster query server and it seems to be a problem because the query was coming with the same results and speed.
In conclusion, we believe that some configuration MYSQL
is incorrect.
Can anyone suggest what configuration or variable MYSQL
might be causing this problem? Because I have no ideas at the moment. I tried searching the web but the results couldn't help me.
SQL 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/3/%')
AND (`level` <= 3)
ORDER BY `main_table`.`position` ASC
source to share
Probably the best indexes are these "compound" indexes:
main_table: INDEX(include_in_menu, is_active, path)
url_rewrite: INDEX(is_system, store_id, category_id, id_path)
Is there any reason to use it LEFT
?
If you still have problems after adding these indexes, please provide SHOW CREATE TABLE
and EXPLAIN SELECT ...
.
source to share