I'm stuck in a situation where I have millions of records and need different connections for the same records. Order terms also have some tricky parts. If I don't apply any sorting, my query performs fast results. But when applying the order clause, it takes too much time to get the result.

Without the Order clause, this results in 5-6 seconds.

When applying order terms, the result is 40-45 seconds

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

NOTE: All joins are important, fields have been removed from the select query for some considerations.

Explanation Summary