wordpress query making site very slow

I have made some changes:

SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
INNER JOIN wp_icl_translations t ON wp_posts.ID = t.element_id
WHERE 1=1 
AND t.element_type = CONCAT('post_', wp_posts.post_type) 
AND wp_postmeta.meta_key = 'post_views_count'
AND wp_posts.post_type="post" 
AND wp_posts.post_status="publish"
AND (
    (t.language_code="fr" AND wp_posts.post_type IN ('post','page','attachment','grille','podcast','equipe')) 
    OR
    wp_posts.post_type NOT IN ('post','page','attachment','grille','podcast','equipe')
)
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC
LIMIT 0, 3

JOIN with wp_icl_translations is changed to INNER JOIN, and one JOIN condition moved to WHERE block.

But, you need to make sure that element_id and element_type columns in the wp_icl_translations table are both indexed. If not, this query will be always slow.

EDIT:
But, the slowness comes from the ORDER BY where you are using a column that can’t be indexed for ordering. Ordering by non-indexed column is very, very, very slow, and considering that you have a large database, if you continue to use ORDER BY as it is now, there is no way to optimize it because you have a complex query as is.

Related Posts: