How can I optimize sql?
SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (7,8,14,104,134,234,289,331,4656,4657,6038,6039,6040,6085,6562,6563) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 2480, 20
I have used caching and object caching. But with the release of a new article, the cache will fail and the server load will be too high. This problem has been plagued for a long time, and it has caused the site to run slowly.
How can I optimize sql?
SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (7,8,14,104,134,234,289,331,4656,4657,6038,6039,6040,6085,6562,6563) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 2480, 20
I have used caching and object caching. But with the release of a new article, the cache will fail and the server load will be too high. This problem has been plagued for a long time, and it has caused the site to run slowly.
The two most obvious things you can do to reduce the load time of this query are the IN statement and the offset in your LIMIT statement.
The larger the number of items you're checking for in the IN statement, the less efficient the query. See: https://stackoverflow/questions/4514697/mysql-in-operator-performance-on-large-number-of-values
The reason the offset is causing performance issues is the inefficiency of mysql's ability to count. See: https://stackoverflow/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down
If you can, I recommend warming the cache after a new article is published. This resets the cache, rebuilding it via a system process rather than by user request. See: https://stackoverflow/questions/434259/what-is-a-warm-up-cache
If you rely on user requests to create the cache, it can lead to users requesting the same result multiple times before the cache is able to be built completely which is not a great position to be in.
If you can get something like varnish in front of your site, that will help a ton. Varnish prevents most requests from hitting php and it's lightning-fast at serving requests.