The issues you are encountering arise from the complexity of the query, the extensive use of EXISTS subqueries, and potential inefficiencies in indexing and optimisation within MySQL.
What Causes MySQL to Stall in the "Statistics" State?
The "statistics" state signifies that MySQL is calculating execution plans for the query. This phase can be particularly resource-intensive when:
Is This the Most Efficient Way to Query the Data?
Not entirely. While EXISTS is suitable for some situations, the sheer volume of subqueries in your example risks overburdening the optimiser. This can lead to significant inefficiencies, particularly when working with billions of records.
To improve this I would:
SELECT COUNT(DISTINCT properties.aid)
FROM properties
LEFT JOIN property_value_strings pvs1
ON pvs1.aid = properties.aid
AND pvs1.deleted_at IS NULL
AND pvs1.attribute_id = 48
AND pvs1.value = 'NC'
LEFT JOIN property_value_strings pvs2
ON pvs2.aid = properties.aid
AND pvs2.deleted_at IS NULL
AND pvs2.attribute_id = 14
AND pvs2.value = 'Wake'
LEFT JOIN property_value_numerics pvn
ON pvn.aid = properties.aid
AND pvn.deleted_at IS NULL
AND pvn.attribute_id = 175
AND pvn.value BETWEEN 200000.0 AND 1000000.0
-- Add additional joins as necessary
WHERE properties.deleted_at IS NULL
GROUP BY properties.aid
HAVING COUNT(pvs1.id) > 0
AND COUNT(pvs2.id) > 0
AND COUNT(pvn.id) > 0;