Closed nathanielks closed 4 years ago
Sometimes the SELECT DISTINCT
query runs in 2 seconds as well, but it's not consistent. That's with SET SESSION query_cache_type=0;
and using SELECT SQL_NO_CACHE
, so I don't know if the query cache is truly disabled or if it's returning cached results.
Thanks @nathanielks, can you confirm this is on a site that doesn't use elasticsearch? Even 20 seconds is an absurd amount of time for a query to be running!
A custom table is a possible solution for sure. Maybe we could just put those keywords into the content or other wp_posts
field used in the default search...
For our purposes we could switch off this query modification and rely on Elasticsearch to speed up the backend in the shorter term.
@roborourke correct, these are queries made directly to the database! No elasticsearch was being used.
Fixed for Altis, going to close this one out for the time being in favour of a more specific issue to toggle this behaviour via a filter.
On sufficiently large
postmeta
tables, these query modifications are causing queries to take upwards of 60-90 seconds to complete. Thepostmeta
table I'm running queries against has 7M+ rows and is 1.56G in size, 924M of data and 634M of index.To set a baseline, I removed the modifications made to the query and ran it, resulting in a 2 second query time. This is our baseline.
GROUP BY
reduces the query to 19 seconds.GROUP BY
but removing thesq_hm_aws_rekognition_keywords
LEFT JOIN
, reduces the query to 25 seconds.GROUP BY
and instead replacing it withSELECT DISTINCT
gets the query down to 20 seconds and should return the same result set as usingGROUP BY
(they effectively do the same).With that being said, would it be possible to instead store rekognition data in its own table that can be properly indexed?