Как оптимизировать запрос с вложенными запросами и order by?

Question

[Антон Шелестов]

Всем привет.
Есть вот такой страшный запрос:

select 
     `doctors`.*, 
     (SELECT file_name FROM images where doctors.id = images.doctor_id AND images.type = "doctor_face") as face_foto,
     (SELECT COUNT(*) FROM doctor_in_filials WHERE doctors.id = doctor_in_filials.doctor_id) as filial_count, 
     (SELECT COUNT(*) FROM reviews as rw_count WHERE doctors.id = rw_count.doctor_id) as reviews_count, 
     (SELECT AVG(rw_avg.rating_doctor) FROM reviews as rw_avg WHERE doctors.id = rw_avg.doctor_id) as doctor_rating 
FROM `doctors` where `doctors`.`id` in 
     (select `doctor_id` from `doctor_in_filials` where `filial_id` in 
          (select `id` from `clinic_filials` where `city_id` = 1) group by `doctor_id`
     )

Результат 6312 всего, Запрос занял 1.3524 сек. из 11 000 срок в таблице doctors, в других таблицах еще больше строк

Если добавить к запросу order by то все, думает долго даже не дожидался.

Мне так кажется что я не правильно создал запрос т.к. не силен в этом, учусь только
Если убрать вложенные select'ы в начале то сортирует быстро, если убрать все where in то тоже сортирует быстро, а вот когда все на месте не хочет.

Answer 1

[Rsa97]

SELECT `d`.*, `i`.`file_name` AS `face_foto`, `dif`.`filial_count`,
       IFNULL(`rw`.`reviews_count`, 0), IFNULL(`rw`.`doctor_rating`, 0)
  FROM (
    SELECT `df`.`doctor_id`, COUNT(*) AS `filial_count`
      FROM `clinic_filials` AS `cf`
      JOIN `doctor_in_filials` AS `df` ON `df`.`filial_id` = `cf`.`id`
      WHERE `cf`.`city_id` = 1
      GROUP BY `df`.`doctor_id`
  ) AS `dif`
  JOIN `doctors` AS `d` ON `d`.`id` = `dif`.`doctor_id`
  LEFT JOIN `images` AS `i` ON `i`.`doctor_id` = `d`.`id`
    AND `i`.`type` = "doctor_face"
  LEFT JOIN (
    SELECT `doctor_id`, COUNT(*) AS `reviews_count`, 
           AVG(`rating_doctor`) AS `doctor_rating`
      FROM `reviews`
      GROUP BY `doctor_id`
  ) AS `rw` ON `rw`.`doctor_id` = `d`.`id`

P.S. И, естественно, EXPLAIN и создание нужных индексов.

Comments

[Антон Шелестов]

Спасибо, сижу изучаю, слегка запутанно для меня))
Попытался засунуть это на phpmyadmin чтобы посмотреть на результат но он заругался на #1054 - Неизвестный столбец 'dif.filial_id' в 'on clause'

[Rsa97]

Антон Шелестов: Поправил, должен быть `df`.`filial_id`

[Антон Шелестов]

Я знал что индексы в нужных местах это круто но никогда ими не пользовался из-за очень редкой работой с БД. Поставил индексы в таблицах и мой первый заработал быстро, сортирует тоже быстро!!!
Спасибо за пример запроса и за совет с индексами, ваш код постараюсь прикрутить к laravel конструктору