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
The problem lies in the combination of LIMIT and ORDER BY.
Without ORDER BY, the query stops once the first ten random rows are selected. The order by query must collect all possible rows, sort them in order, and then return only the first 10 rows.
Considering that "postponed_consultation_datetime" is a calculated field, there is really no way to make it faster.