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
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;
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).
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 ;
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]