Здравствуйте.
Прошу помочь с запросом из-за которого сильно грузится процессор.
SHOW FULL PROCESSLIST показывает для него состояние "Copying to tmp table"
Сам запрос (сильно не ругайте).
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT
`vuzes`.`id`, `vuzes`.`name`, `vuzes`.`subj_id`, `vuzes`.`city_id`, `metros`.`name` AS metro,
`vuzes`.`gos`, `vuzes`.`hostel`, `vuzes`.`military`, `vuzes`.`vedom`,
`user2vuz`.`u_id`, IFNULL(b.opinions,0) AS opinions, IFNULL(s.specs, 0) AS specs,
IF(`vuzes`.`packetEnd`>DATE(NOW()), "sert", "") AS packet, `vuzes`.`noAbitur`, `vuzes`.`ege`
FROM
`vuzes` LEFT JOIN
`specs` ON `specs`.`vuz_id` = `vuzes`.`id` LEFT JOIN
`general`.`metros` ON `vuzes`.`metro_id`=`metros`.`id` LEFT JOIN
`vuz`.`user2vuz` ON `user2vuz`.`vuz_id`=`vuzes`.`id` LEFT JOIN (
SELECT `vuz_id` , count( * ) AS opinions
FROM `vuz`.`opinions`
GROUP BY `vuz_id`
) b ON b.`vuz_id` = `vuzes`.`id` LEFT JOIN (
SELECT `vuz_id` , count(*) AS specs
FROM `vuz`.`specs`
GROUP BY `vuz_id`
) s ON s.`vuz_id` = `vuzes`.`id`
WHERE `delReason`="" AND `vuzes`.`subj_id`=78 AND `specs`.`free`!=0
GROUP BY `vuzes`.`id` ORDER BY `packet` DESC, `vuzes`.`rating` DESC LIMIT 0, 30;
EXPLAIN в приложении
Профилирование
авторstarting 0.000039
checking query cache for query 0.000169
Opening tables 0.000106
System lock 0.000039
Table lock 0.000154
optimizing 0.000018
statistics 0.000028
preparing 0.000030
executing 0.000027
Sorting result 0.000021
Sending data 0.000973
optimizing 0.000025
statistics 0.000031
preparing 0.000026
executing 0.000024
Sorting result 0.000022
Sending data 0.018629
init 0.000231
optimizing 0.000040
statistics 0.000599
preparing 0.000054
Creating tmp table 0.000189
executing 0.000022
Copying to tmp table 0.879500
Sorting result 0.000135
Sending data 0.000257
end 0.000021
removing tmp table 0.000093
end 0.000020
query end 0.000017
freeing items 0.002168
removing tmp table 0.000029
closing tables 0.000015
removing tmp table 0.000019
closing tables 0.000036
logging slow query 0.000014
cleaning up 0.000022
Заранее спасибо за ответ, без оскорблений)
