Good day, friends. Help optimize the query to filter the data. There is such a request, it now works 1.5 seconds. Is it possible to speed up his work?

EXPLAIN SELECT r.id, o.name AS objectName, (SELECT MAX(d2.`valuePercent`) FROM discounts AS d2 JOIN `rooms` AS r2 ON d2.`roomId` = r2.id WHERE r2.objectId = r.objectId) AS `valuePercent`, d.type, o.stars, o.`infoAboutObject`, o.id AS objectId, o.url, r.`price`, o.`address`, o.`image`, c.`name` AS cityName, o.longitudeGps, o.latitudeGps, (SELECT GROUP_CONCAT(DISTINCT otp1.`class` SEPARATOR ',') FROM objects_types_pluses AS otp1 JOIN objects_objects_types_pluses AS ot1 ON ot1.`objectTypePlusesId` = otp1.id WHERE ot1.`objectId` = o.id) AS className, (SELECT GROUP_CONCAT(DISTINCT otp1.`name` SEPARATOR ',') FROM objects_types_pluses AS otp1 JOIN objects_objects_types_pluses AS ot1 ON ot1.`objectTypePlusesId` = otp1.id WHERE ot1.`objectId` = o.id) AS pluseName, (SELECT COUNT(rev.id) FROM reviews AS rev WHERE rev.objectId = o.`id`) AS reviewCount, (SELECT GROUP_CONCAT(DISTINCT os1.`name` SEPARATOR ',') FROM objects_servises AS os1 JOIN objects_relation_objects_servises AS oros ON oros.`objectServiceId` = os1.id WHERE oros.`objectId` = o.id) AS objectsServisesName, GROUP_CONCAT(DISTINCT os.`name` SEPARATOR ',') AS objectsServisesName, o.raitingValue FROM objects AS o JOIN cities AS c ON o.`cityId` = c.`id` LEFT JOIN objects_objects_types_pluses AS op ON op.`objectId` = o.id LEFT JOIN `objects_types_pluses` AS otp ON op.`objectTypePlusesId` = otp.`id` JOIN rooms AS r ON o.`id` = r.`objectId` LEFT JOIN discounts AS d ON r.`id` = d.`roomId` LEFT JOIN objects_relation_objects_servises AS oros ON o.`id` = oros.`objectId` LEFT JOIN objects_servises AS os ON oros.objectServiceId = os.`id` WHERE ( d.`valuePercent` = ( SELECT MAX(d1.`valuePercent`) FROM discounts AS d1 JOIN `rooms` AS r1 ON d1.`roomId` = r1.id WHERE r1.objectId = r.objectId ) OR d.`valuePercent` IS NULL ) AND c.countryId = 1 GROUP BY o.id LIMIT 7 OFFSET 0

Result with explain

  • To understand how a query works inside MySQL, you can use EXPLAIN. Please attach to the question the result of the query EXPLAIN <body-optimized-query>. More details about EXPLAIN can be read here dev.mysql.com/doc/refman/5.7/en/explain-output.html - Andrey Mindubaev
  • Thank you, added screen. - Nick
  • In the row about the rooms table in the type column is ALL , and in extra - Using temporary; Using filesort Using temporary; Using filesort . And in the GROUP BY query itself, there is a column from the objects table. The rooms and objects tables are linked through JOIN rooms AS r ON o. id` = r. objectId . Those. there may be something wrong with the rooms or objects table indexes. Please the results of SHOW INDEX FROM rooms and SHOW INDEX FROM objects to the question, please - Andrey Mindubaev
  • But, in general, this can still be remotely optimized for a long time. In any case, you need to look at the result of the query EXPLAIN. For example, look at the lines where the type is the value ALL and somehow modify the query. For example, discounts join via LEFT JOIN, and in WHERE there is a condition with a column of this table. - Andrey Mindubayev

0