S
S
Sergey Kornienko2017-04-05 14:43:43
MySQL
Sergey Kornienko, 2017-04-05 14:43:43

Question about using indexes in different versions of MySQL?

There is a complex compound query of the form:

SELECT `c`.*, `r`.*, `ci`.*, `i`.*, `u`.*, `ca`.`Category_Weight` AS Category_Parent_Weight, `ca`.`Category_Alias` AS Category_Parent_Alias
FROM (`ct_items` i)
LEFT JOIN `ct_categories` c ON `i`.`Category_ID` = `c`.`Category_ID`
LEFT JOIN `ct_categories` ca ON `c`.`Category_Group_Id` = `ca`.`Category_ID`
LEFT JOIN `ct_regions` r ON `i`.`Region_ID` = `r`.`Region_ID`
LEFT JOIN `ct_cities` ci ON `i`.`City_ID` = `ci`.`City_ID`
LEFT JOIN `ct_users` u ON `i`.`User_ID` = `u`.`id`
WHERE `Item_status` =  'active'
AND `User_ID` =  '1'
AND `Item_ID` <> '5869135'
LIMIT 4

The database on the servers is exactly the same, the ct_items table is 6 million records, the MyISAM engine.
The 1st server has Percona Server (GPL), Release 11, Revision f60191c (5.7.17-11-
log
a26ea7fe06224188a57aff64803c86f4.png
) )
explain:
e2d2717d66cf4c0eb759f5d0f23dbffe.png
Why the Account_ID index is not used on the second server, but "Using index condition; Using where; Using MRR"
and the number of rows in the first case is 4, and in the second 6 million. Because of this, this query on the second server is very strong slows down...

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question