DB error with too many appointments: WordPress database error The SELECT would examine more than MAX_JOIN_SIZE rows
Since a few weeks a client has been complaining that their website isn’t blocking reservations on slots that should be fully/partially booked anymore. The calendar is just showing everything as completely empty/free. I haven’t been able to reproduce the issue, but turned on logging and saw the following error popping up.
WordPress database error The SELECT would examine more than MAX_JOIN_SIZE rows;
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay for query
SELECT p.ID FROM www0_posts p LEFT JOIN www0_postmeta _appointment_product_id ON p.ID = _appointment_product_id.post_id
AND _appointment_product_id.meta_key = ‘_appointment_product_id’
LEFT JOIN www0_postmeta _appointment_start ON p.ID = _appointment_start.post_id
AND _appointment_start.meta_key = ‘_appointment_start’ LEFT JOIN www0_postmeta _appointment_end ON p.ID = _appointment_end.post_id
AND _appointment_end.meta_key = ‘_appointment_end’ LEFT JOIN www0_postmeta _appointment_all_day ON p.ID = _appointment_all_day.post_id
AND _appointment_all_day.meta_key = ‘_appointment_all_day’
WHERE 1=1 AND p.post_type = ‘wc_appointment’
AND _appointment_product_id.meta_value IN (‘1011’)
AND p.post_status IN (‘unpaid’,’paid’,’pending-confirmation’,’confirmed’,’complete’,’in-cart’)
AND ( (_appointment_start.meta_value < ‘20230121000100’ AND
_appointment_end.meta_value > ‘20230120000000’ AND
_appointment_all_day.meta_value = ‘0’
) OR (
_appointment_start.meta_value <= ‘20230121000000’ AND
_appointment_end.meta_value >= ‘20230120000000’ AND
_appointment_all_day.meta_value = ‘1’
) ) ORDER BY p.post_date DESC ; made by do_action(‘wp_ajax_wc_appointments_get_slots’),
WP_Hook->do_action, WP_Hook->apply_filters, WC_Appointments_Admin_Ajax->get_time_slots_for_date,
WC_Appointment_Data_Store::get_all_existing_appointments, WC_Appointment_Data_Store::get_appointments_for_objects,
WC_Appointment_Data_Store::get_appointments_for_objects_query, WC_Appointment_Data_Store::get_appointment_ids_by
Reported here.