Saya terperangkap dalam situasi di mana saya mempunyai berjuta-juta rekod dan memerlukan sambungan yang berbeza untuk rekod yang sama. Terma pesanan juga mempunyai beberapa bahagian yang rumit. Jika saya tidak menggunakan sebarang pengisihan, pertanyaan saya menghasilkan hasil yang pantas. Tetapi apabila menggunakan klausa pesanan, ia mengambil masa terlalu lama untuk mendapatkan hasilnya.
Tanpa klausa Pesanan, ini menghasilkan 5-6 saat.
Apabila menggunakan syarat pesanan, hasilnya adalah 40-45 saat
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
Nota: Semua gabungan adalah penting, medan telah dialih keluar daripada pertanyaan pilihan untuk beberapa pertimbangan.
Ringkasan Penerangan
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Masalahnya ialah gabungan LIMIT dan ORDER BY.
Tanpa ORDER BY, pertanyaan akan berhenti sebaik sahaja sepuluh baris rawak pertama dipilih. Susunan mengikut pertanyaan mesti mengumpulkan semua baris yang mungkin, mengisihnya mengikut tertib, dan kemudian mengembalikan hanya 10 baris pertama.
Memandangkan "postponed_consultation_datetime" ialah medan yang dikira, tidak ada cara untuk menjadikannya lebih pantas.