1

I already added a composite index but it still took 5 seconds to finish these 2 queries.

SELECT COUNT( DISTINCT( wpp.id ) ) 
FROM `wpdgls_posts` wpp JOIN `wpdgls_postmeta` wpm ON wpp.id = wpm.post_id
WHERE wpm.meta_key = '_form_id' AND wpm.meta_value = '14' ;

SELECT COUNT( DISTINCT( wpp.id ) ) 
FROM `wpdgls_posts` wpp JOIN `wpdgls_postmeta` wpm ON   wpp.id = wpm.post_id
WHERE wpm.meta_key = '_form_id' AND wpm.meta_value = '14' ;

Here are my index:

KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191)),
KEY `meta_value` (`meta_value`(255)),
KEY `wp_meta` (`post_id`,`meta_key`,`meta_value`(8))

Should I use FULLTEXT index on meta_value?

  • Querying by post meta is notorious for being slow. Have you considered creating a taxonomy instead? That type of query is much faster. It's also best practice to use WP's built-in functions so instead of a straight-up MySQL query you can use WP_Query() to select specific posts. – WebElaine Jan 16 at 14:49
  • I am not familiar with taxonomy. What does it do? Our issue right now is there are numerous queries that gets stucked from the get_sub_count function (ninja form). Like this query below: SELECT DISTINCT( COUNT( wpp.id ) ) AS sub_count FROM wpdgls_posts wpp JOIN wpdgls_postmeta wpm ON wpp.id = wpm.post_id WHERE wpm.meta_key = '_form_id' AND wpm.meta_value = '18' – Driggs Alonzo Feb 5 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.