How to optimize this wordpress mysql query?


Problem :

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;


Solution :

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 ;

Mysql Tutorials

Mysql Howto..

How to generate a bunch of Fake Users into MySQL Database

How to write query for this?

How do I convert a mySQL timestamp into RFC-822 using Perl?

how to update a specific field(column) of a row by that row's autoincrement value with some prefix in mysql?

How to resolve incorrect SQL syntax error when using “Dimension Lookup / Update” with MySql?

How can I download Apache server, PHP, and MySQL or other database on my Windows 7 machine? [closed]

How to reduce MySQL query execution time from 90 seconds to less than 5 seconds or even one second

How to insert the current system date into a db column using mysql

How to query with a where clause in mysql json table

How to save query results column into a column in table in same database

How to display monthwise report in mysql

How to sort mysql result but show specific result first

How to count number of times team is highest scorer in a game from a MySQL cross table?

How to insert data into text area from mysql

How can I calculate the top % daily price changes using MySQL?

how-to query mysql utf-8 table using ASCII

How to change the database which is used to store liferay content?

MYSQL innodb fulltext search not showing all results

How can I compare request.getParameter(string) in Controller class with MySQL database?

How to connect Android app with MySQL database through PHP

How to insert text content in most secure way from textarea in MYSQL [closed]

MySql-How to parse date which is attached with another string

How to implement a mapped keyword match with multiple keywords and preference based on number of hits?

How to use inner join statement with Hibernate

how to output a standings table on the fly from a mysql table of football [soccer] results?

How do I generate a unique URL for each row in a table?

How to update a mysql database without reloading page

Showing images from path in mysql database

MySQL how to set a column to reference columns from another table

How can I assign the values of elements using simpleXML to this array?