CODEWITHSUNDEEP
mysqlwordpressquery-optimization
chifliiiii
chifliiiii

Reputation: 2339

How to optimize this wordpress mysql query?

Im importing from xml files into a wordpress database. I have towns and locations imported as meta values of each post.

I dont have a predefind list of towns and locations so i need to get all locations (easy thing) and get all towns inside those locations ( im having problems here)

The query i have now is:

SELECT t1.meta_value 
FROM wp_postmeta t1  
INNER JOIN wp_postmeta t2 ON (t1.post_id = t2.post_id) 
WHERE t2.meta_key = '_prop_loc'
    AND t2.meta_value="Málaga" 
    AND t1.meta_key = '_prop_town' 
GROUP BY t1.meta_value

For example if i try to get all towns in "Málaga" location i ended using this query. Testing it on localhost the query runs 

showing records 0 - 29 ( 101 total, total time 1.3289 seg)

If I take out the group by the time decreases but of course I get duplicate rows. If i use select distinct with order ASC the times goes up again.

I do want to have the results ordered, but a query of more than a second is not ok

wp_postmeta table:

CREATE TABLE `wp_postmeta` (
  `meta_id`    bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id`    bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key`   varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY            (`meta_id`),
          KEY `post_id`  (`post_id`),
          KEY `meta_key` (`meta_key`)
) ENGINE=***  DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 779

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I assume that you haven't added any more indexes on the default WP design.

You can add an index on (meta_key, meta_value, post_id)

and one on (meta_key, post_id, meta_value).

Update: meta_value is a text column so this isn't an option. You could try making the first a partial indexes on that column and not including it in the second index:

ALTER TABLE wp_postmeta 
  ADD INDEX meta_key__meta_value__post_id__IX
    (meta_key, meta_value(20), post_id),
  ADD INDEX meta_key__post_id__IX
    (meta_key, post_id) ;

You could also try this rewriting of the query:

SELECT t1.meta_value 
FROM wp_postmeta t1  
WHERE t1.meta_key = '_prop_town'
  AND EXISTS
      ( SELECT *
        FROM wp_postmeta t2 
        WHERE t2.meta_key = '_prop_loc'
          AND t2.meta_value = "Málaga" 
          AND t2.post_id = t1.post_id
      ) 
GROUP BY t1.meta_value ;

Upvotes: 2

Related Questions