La clause de commande dans la requête prend trop de temps de chargement MySQL

Question

Je suis coincé dans une situation où j'ai des millions d'enregistrements et j'ai besoin de connexions différentes pour les mêmes enregistrements. Les conditions de commande comportent également des parties délicates. Si je n'applique aucun tri, ma requête donne des résultats rapides. Mais lors de l’application de la clause d’ordre, il faut trop de temps pour obtenir le résultat.

Sans la clause Order, cela donne 5-6 secondes.

Lors de l'application des conditions de commande, le résultat est de 40-45 secondes

SELECT 
  forms_values.id,
  CASE
    WHEN forms_values.appointment_type = 2 
    AND user_patient_assinged_to_doctor.start_time IS NOT NULL 
    THEN 
    CASE
      WHEN patient_responded_tags_logs.tag_set_at IS NOT NULL 
      THEN 
      CASE
        WHEN UNIX_TIMESTAMP(
          CONVERT_TZ(
            patient_responded_tags_logs.tag_set_at,
            "+00:00",
            "-06:00"
          )
        ) > UNIX_TIMESTAMP(
          CONVERT_TZ(
            STR_TO_DATE(
              CONCAT(
                user_patient_assinged_to_doctor.date,
                " ",
                user_patient_assinged_to_doctor.start_time
              ),
              "%Y-%m-%d %h:%i %p"
            ),
            "+00:00",
            "-06:00"
          )
        ) 
        THEN UNIX_TIMESTAMP(
          CONVERT_TZ(
            patient_responded_tags_logs.tag_set_at,
            "+00:00",
            "-06:00"
          )
        ) 
        ELSE UNIX_TIMESTAMP(
          CONVERT_TZ(
            STR_TO_DATE(
              CONCAT(
                user_patient_assinged_to_doctor.date,
                " ",
                user_patient_assinged_to_doctor.start_time
              ),
              "%Y-%m-%d %h:%i %p"
            ),
            "+00:00",
            "-06:00"
          )
        ) 
      END 
      ELSE UNIX_TIMESTAMP(
        CONVERT_TZ(
          STR_TO_DATE(
            CONCAT(
              user_patient_assinged_to_doctor.date,
              " ",
              user_patient_assinged_to_doctor.start_time
            ),
            "%Y-%m-%d %h:%i %p"
          ),
          "+00:00",
          "-06:00"
        )
      ) 
    END 
    ELSE 
    CASE
      WHEN patient_responded_tags_logs.tag_set_at IS NOT NULL 
      THEN 
      CASE
        WHEN UNIX_TIMESTAMP(
          CONVERT_TZ(
            patient_responded_tags_logs.tag_set_at,
            "+00:00",
            "-06:00"
          )
        ) > UNIX_TIMESTAMP(forms_values.created_at) 
        THEN UNIX_TIMESTAMP(
          CONVERT_TZ(
            patient_responded_tags_logs.tag_set_at,
            "+00:00",
            "-06:00"
          )
        ) 
        ELSE UNIX_TIMESTAMP(forms_values.created_at) 
      END 
      ELSE UNIX_TIMESTAMP(forms_values.created_at) 
    END 
  END AS "consultation_date_time_ordering",
  CASE
    WHEN forms_values.appointment_type = 2 
    AND user_patient_assinged_to_doctor.start_time IS NOT NULL 
    THEN UNIX_TIMESTAMP(
      CONVERT_TZ(
        STR_TO_DATE(
          CONCAT(
            user_patient_assinged_to_doctor.date,
            " ",
            user_patient_assinged_to_doctor.start_time
          ),
          "%Y-%m-%d %h:%i %p"
        ),
        "+00:00",
        "-06:00"
      )
    ) 
    ELSE UNIX_TIMESTAMP(forms_values.created_at) 
  END AS "consultation_date_time" ,
  CASE
    WHEN forms_values.is_postpone = '1' 
    OR forms_values.is_completed = '8' 
    THEN 
    CASE
      WHEN UNIX_TIMESTAMP(
        CONVERT_TZ(
          STR_TO_DATE(
            CONCAT(UTC_DATE(), ' ', UTC_TIME()),
            '%Y-%m-%d %h:%i:%s'
          ),
          '+00:00',
          '-06:00'
        )
      ) < UNIX_TIMESTAMP(
        my_list_postpone.postponed_date
      ) 
      THEN 0 
      ELSE 1 
    END 
    ELSE 1 
  END AS "postponed_consultation_ordering"
FROM
  `forms_values` 
  LEFT JOIN `forms_values_completed_status_details` 
    ON `forms_values_completed_status_details`.`form_value_id` = `forms_values`.`id` 
  /*INNER JOIN `users` 
    ON `users`.`id` = `forms_values`.`patient_id` 
  LEFT JOIN `users` AS `doctors` 
    ON `doctors`.`id` = `forms_values`.`doctor_id`*/ 
  LEFT JOIN `user_patient_assinged_to_doctor` 
    ON `user_patient_assinged_to_doctor`.`form_value_id` = `forms_values`.`id` 
  INNER JOIN `states_countries` 
    ON `forms_values`.`state` = `states_countries`.`id` 
  LEFT JOIN `user_payment_history` 
    ON `user_payment_history`.`form_value_id` = `forms_values`.`id` 
  LEFT JOIN `emailed_tags_logs` 
    ON `emailed_tags_logs`.`form_value_id` = `forms_values`.`id` 
    AND `emailed_tags_logs`.`id` = 
    (SELECT 
      emailed_tags_logs.id 
    FROM
      emailed_tags_logs 
    WHERE emailed_tags_logs.form_value_id = forms_values.id 
      AND emailed_tags_logs.id = 
      (SELECT 
        emailed_tags_logs1.id AS emtid 
      FROM
        emailed_tags_logs AS emailed_tags_logs1 
      WHERE emailed_tags_logs1.form_value_id = forms_values.id 
      ORDER BY emailed_tags_logs1.created_at DESC 
      LIMIT 1) 
      AND emailed_tags_logs.status IN (1, 3) 
      AND emailed_tags_logs.is_pt_responded = "0" 
    ORDER BY emailed_tags_logs.created_at DESC 
    LIMIT 1) 
  LEFT JOIN `my_list_assign_doctor` 
    ON `my_list_assign_doctor`.`form_value_id` = `forms_values`.`id` 
    AND `my_list_assign_doctor`.`id` = 
    (SELECT 
      my_list_assign_doctor.id 
    FROM
      my_list_assign_doctor 
    WHERE my_list_assign_doctor.form_value_id = forms_values.id 
      AND my_list_assign_doctor.status IN (1, 2) 
      AND my_list_assign_doctor.prior_type = "others" 
    ORDER BY my_list_assign_doctor.created_at DESC 
    LIMIT 1) 
  LEFT JOIN `my_list_assign_doctor` AS `my_list_postpone` 
    ON `my_list_postpone`.`form_value_id` = `forms_values`.`id` 
    AND `forms_values`.`is_postpone` IN ('1', '2') 
    AND `my_list_postpone`.`id` = 
    (SELECT 
      my_list_assign_doctor.id 
    FROM
      my_list_assign_doctor 
    WHERE my_list_assign_doctor.form_value_id = forms_values.id 
      AND my_list_assign_doctor.prior_type = "postpone" 
    ORDER BY my_list_assign_doctor.created_at DESC 
    LIMIT 1) 
  LEFT JOIN `users` AS `partner` 
    ON `user_payment_history`.`std_partner_id` = `partner`.`id` 
  LEFT JOIN `patient_responded_tags_logs` 
    ON `patient_responded_tags_logs`.`form_value_id` = `forms_values`.`id` 
    AND `patient_responded_tags_logs`.`status` = '1' 
  LEFT JOIN `user_subscriptions` 
    ON `user_subscriptions`.`user_payment_history_id` = `user_payment_history`.`id` 
    AND `user_payment_history`.`form_value_id` = `forms_values`.`id` 
ORDER BY 
postponed_consultation_ordering DESC,
  `consultation_date_time` DESC 
LIMIT 10 OFFSET 0

Remarque : toutes les jointures sont importantes, les champs ont été supprimés de la requête de sélection pour certaines considérations.

Résumé explicatif

Answer 1

Le problème est la combinaison de LIMIT et ORDER BY.

Sans ORDER BY, la requête s'arrête une fois les dix premières lignes aléatoires sélectionnées. La requête order by doit collecter toutes les lignes possibles, les trier dans l'ordre, puis renvoyer uniquement les 10 premières lignes.

Considérant que "postponed_consultation_datetime" est un champ calculé, il n'y a vraiment aucun moyen de le rendre plus rapide.