WordPress MySQL Query takes too much time to execute

3 Views

I am using WordPress 4.4.2.

My Question is : In wordpress database I have a postmeta table with about 314,000 Records. When I am trying to execute following query (Build by wordpress) it takes about 5 – 10 Seconds to execute. I have about this kind of query in a single page like 2 – 3 times so takes 20 – 30 seconds to execute a single page:

Read More
SELECT hf_posts.ID
FROM hf_posts 
INNER JOIN hf_term_relationships
ON (hf_posts.ID = hf_term_relationships.object_id)

INNER JOIN hf_postmeta
ON ( hf_posts.ID = hf_postmeta.post_id ) 

INNER JOIN hf_postmeta AS mt1
ON ( hf_posts.ID = mt1.post_id ) 

INNER JOIN hf_postmeta AS mt2
ON ( hf_posts.ID = mt2.post_id )


WHERE 1=1 
AND ( hf_term_relationships.term_taxonomy_id IN (20) )
AND ( hf_postmeta.meta_key = 'match-date' 
AND ( ( mt1.meta_key = 'match-date'
AND CAST(mt1.meta_value AS SIGNED) >= '1460091523' ) 
AND ( mt2.meta_key = 'awaygame'
AND CAST(mt2.meta_value AS CHAR) = '0' ) ) )
AND hf_posts.post_type = 'match'
AND ((hf_posts.post_status = 'publish'
OR hf_posts.post_status = 'future'))
GROUP BY hf_posts.ID
ORDER BY hf_postmeta.meta_value ASC
LIMIT 0, 1

I checked everything and I concluded that main problem is in postmeta table. Is there any solution that I can speed up this execution of query.

Explain output :

enter image description here

SHOW CREATE TABLE OUTPUT : 

CREATE TABLE `hf_postmeta` (
 `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `meta_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `meta_value` longtext COLLATE utf8_unicode_ci,
 PRIMARY KEY (`meta_id`),
 KEY `post_id` (`post_id`),
 KEY `meta_key` (`meta_key`(191)),
 KEY `meta_id` (`meta_id`),
 KEY `post_id_2` (`post_id`),
 KEY `meta_key_2` (`meta_key`),
 KEY `meta_key_3` (`meta_key`)
) ENGINE=InnoDB AUTO_INCREMENT=326766 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Looking forward to see your reply.

Regards,
Chirag

Post Views: 3

Related posts

1 comment

  1. (I had to prettyprint it to understand it…)

    SELECT  p.ID
    FROM  hf_posts AS p
    INNER JOIN  hf_term_relationships AS r ON (p.ID = r.object_id)
    INNER JOIN  hf_postmeta AS mt0 ON ( p.ID = mt0.post_id )
    INNER JOIN  hf_postmeta AS mt1 ON ( p.ID = mt1.post_id )
    INNER JOIN  hf_postmeta AS mt2 ON ( p.ID = mt2.post_id )
    WHERE  1=1
      AND  r.term_taxonomy_id IN (20)
      AND  mt0.meta_key = 'match-date'
      AND  mt1.meta_key = 'match-date'
      AND  CAST(mt1.meta_value AS SIGNED) >= '1460091523'
      AND  mt2.meta_key = 'awaygame'
      AND  CAST(mt2.meta_value AS CHAR) = '0'
      AND  p.post_type = 'match'
      AND  (      p.post_status = 'publish'
              OR  p.post_status = 'future'
           )
    GROUP BY  p.ID
    ORDER BY  mt0.meta_value ASC
    LIMIT  0, 1

    If possible, get rid of the first instance of hf_postmeta. It seems to be there only to provide meta_value for ordering, yet you are fetching the same value via mt1.

    If possible, get rid of hf_posts. It seems to provide nothing. Instead of p.ID, you could use r.object_id, which is known to be the same.

    3 tables instead of 5 should help performance.

    There are 3 redundant indexes in hf_postmeta, but you don’t even have the two you need:

    INDEX(post_id, meta_key)
INDEX(meta_key, post_id)

    And hf_term_relationships needs

    INDEX(term_taxonomy_id, object_id)
INDEX(object_id, term_taxonomy_id)

Comments are closed.