Even using cache, the search was still very slow. So, I conducted some tests to identify the issue.
Let’s consider that I have a NETSONS hosting, where on Netsons shared hosting services, the execution of the database utilizes additional resources provided by Netsons, which are not accounted for in the hosting service’s resource monitor.
In this case, we can verify that the code is exceeding its limits multiple times.
[Mon May 15 15:45:17 2023] ler***** LIMIT_ENFORCED period long av.value, field cpu value 52/limit 50 loadavg(2.18 2.42 2.26 6/1019 886343) vmstat( 1 0 549080 6669960 34484 36766852 0 0 439 411 0 1 36 10 54 0 0)cpu = 0.513667, read = 0, write = 14784
[Mon May 15 15:45:18 2023] ler***** LIMIT_ENFORCED period long av.value, field cpu value 51/limit 50 loadavg(2.09 2.40 2.25 4/1026 886363) vmstat( 4 0 549080 6610484 34484 36793232 0 0 439 411 0 1 36 10 54 0 0)cpu = 0.507372, read = 0, write = 14784
[Mon May 15 15:45:19 2023] ler***** LIMIT_ENFORCED period long av.value, field cpu value 51/limit 50 loadavg(2.09 2.40 2.25 7/1026 886378) vmstat( 5 0 549080 6540852 34484 36797076 0 0 439 411 0 1 36 10 54 0 0)cpu = 0.501083, read = 0, write = 14784
[Mon May 15 15:46:19 2023] Restrict mode is over for user ler*****
I have also checked the presence of so-called slow queries that took more than 3 seconds to execute.
The execution of queries usually takes milliseconds, therefore queries that exceed 3 seconds are considered slow.
In my case, I see that there are queries that even exceed 45 seconds for execution.
# Time: 230515 15:38:25
# User@Host: ler**********[ler**********] @ localhost []
# Thread_id: 12676555 Schema: ler********** QC_hit: No
# Query_time: 45.002961 Lock_time: 0.000580 Rows_sent: 0 Rows_examined: 25461902
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1684157905;
SELECT wpan_posts.ID
FROM wpan_posts LEFT JOIN wpan_postmeta ON ( wpan_posts.ID = wpan_postmeta.post_id ) LEFT JOIN wpan_postmeta AS mt1 ON ( wpan_posts.ID = mt1.post_id ) LEFT JOIN wpan_postmeta AS mt2 ON ( wpan_posts.ID = mt2.post_
id ) LEFT JOIN wpan_postmeta AS mt3 ON ( wpan_posts.ID = mt3.post_id ) LEFT JOIN wpan_postmeta AS mt4 ON ( wpan_posts.ID = mt4.post_id AND mt4.meta_key = 'hp_booking_min_length' ) LEFT JOIN wpan_postmeta AS mt5 ON ( wpan_posts.ID = mt5.
post_id ) LEFT JOIN wpan_postmeta AS mt6 ON ( wpan_posts.ID = mt6.post_id AND mt6.meta_key = 'hp_booking_offset' ) LEFT JOIN wpan_postmeta AS mt7 ON ( wpan_posts.ID = mt7.post_id ) LEFT JOIN wpan_postmeta AS mt8 ON ( wpan_posts.ID = mt8
.post_id AND mt8.meta_key = 'hp_booking_window' )
WHERE 1=1 AND (
wpan_postmeta.meta_key = 'hp_featured'
AND
(
( mt1.meta_key = 'hp_latitude' AND CAST(mt1.meta_value AS DECIMAL(10,6)) BETWEEN '40.912355' AND '41.454979' )
AND
( mt2.meta_key = 'hp_longitude' AND CAST(mt2.meta_value AS DECIMAL(10,6)) BETWEEN '9.020995' AND '9.737159' )
AND
(
( mt3.meta_key = 'hp_booking_min_length' AND CAST(mt3.meta_value AS SIGNED) <= '1' )
OR
mt4.post_id IS NULL
)
AND
(
( mt5.meta_key = 'hp_booking_offset' AND CAST(mt5.meta_value AS SIGNED) <= '15' )
OR
mt6.post_id IS NULL
)
AND
(
( mt7.meta_key = 'hp_booking_window' AND CAST(mt7.meta_value AS SIGNED) >= '15' )
OR
mt8.post_id IS NULL
)
)
) AND wpan_posts.post_type = 'hp_listing' AND ((wpan_posts.post_status = 'publish')) AND ( SELECT COUNT(*) FROM wpan_posts AS bookings
INNER JOIN wpan_postmeta AS start_times ON ( bookings.ID = start_times.post_id )
INNER JOIN wpan_postmeta AS end_times ON ( bookings.ID = end_times.post_id )
WHERE bookings.post_status IN ( 'publish', 'private', 'draft', 'pending' ) AND bookings.post_type = 'hp_booking'
AND bookings.post_parent = wpan_posts.ID
AND start_times.meta_key = 'hp_start_time' AND end_times.meta_key = 'hp_end_time'
AND (
( CAST( start_times.meta_value AS SIGNED ) BETWEEN 1685404800 and 1685491198 )
OR ( CAST( end_times.meta_value AS SIGNED ) BETWEEN 1685404801 and 1685491199 )
OR (
