How do I optimize my query?

Question

I've written a query to pull a report from a WordPress database. The query is unbelievable slow so I want to optimize it. I've done a bit of research and am having trouble understanding what is the best way top speed up the query (which, even though I wrote, I barely understand what I did.) Can someone point me in the right direction toward optimizing this slow query.

SELECT
wp1.meta_value as firstName,
wp2.meta_value as middleName,
wp3.meta_value as lastName,
wp5.meta_value as email2,
wp6.meta_value as address,
wp7.meta_value as city,
wp8.meta_value as state,
wp9.meta_value as zip,
wp10.meta_value as country,
wp11.meta_value as phone,
wp12.meta_value as dateOfBirth
FROM wp99_usermeta wp1
JOIN wp99_usermeta wp2 ON wp1.user_id = wp2.user_id and wp2.meta_key = 'middle_name'
JOIN wp99_usermeta wp3 ON wp1.user_id = wp3.user_id and wp3.meta_key = 'last_name'
JOIN wp99_usermeta wp5 ON wp1.user_id = wp5.user_id and wp5.meta_key = 'email2'
JOIN wp99_usermeta wp6 ON wp1.user_id = wp6.user_id and wp6.meta_key = 'street_address'
JOIN wp99_usermeta wp7 ON wp1.user_id = wp7.user_id and wp7.meta_key = 'city'
JOIN wp99_usermeta wp8 ON wp1.user_id = wp8.user_id and wp8.meta_key = 'state'
JOIN wp99_usermeta wp9 ON wp1.user_id = wp9.user_id and wp9.meta_key = 'zip'
JOIN wp99_usermeta wp10 ON wp1.user_id = wp10.user_id and wp10.meta_key = 'country'
JOIN wp99_usermeta wp11 ON wp1.user_id = wp11.user_id and wp11.meta_key = 'phone'
JOIN wp99_usermeta wp12 ON wp1.user_id = wp12.user_id and wp12.meta_key = 'date_of_birth'
WHERE wp1.meta_key = 'first_name'

The default wp_usermeta table structure:

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

Trying the ALTER TABLE suggestion. This is giving me an error at line 5

ALTER TABLE wp99_usermeta ADD INDEX meta_key__user_id (meta_key, user_id) ;

SELECT
wp1.meta_value as firstName,
wp2.meta_value as middleName,
wp3.meta_value as lastName,
wp5.meta_value as email2,
wp6.meta_value as address,
wp7.meta_value as city,
wp8.meta_value as state,
wp9.meta_value as zip,
wp10.meta_value as country
FROM wp99_usermeta wp1
JOIN wp99_usermeta wp2 ON wp1.user_id = wp2.meta_key__user_id = 'middle_name'
JOIN wp99_usermeta wp3 ON wp1.user_id = wp3.meta_key__user_id = 'last_name'
JOIN wp99_usermeta wp5 ON wp1.user_id = wp5.meta_key__user_id = 'email2'
JOIN wp99_usermeta wp6 ON wp1.user_id = wp6.meta_key__user_id = 'street_address'
JOIN wp99_usermeta wp7 ON wp1.user_id = wp7.meta_key__user_id = 'city'
JOIN wp99_usermeta wp8 ON wp1.user_id = wp8.meta_key__user_id = 'state'
JOIN wp99_usermeta wp9 ON wp1.user_id = wp9.meta_key__user_id = 'zip'
JOIN wp99_usermeta wp10 ON wp1.user_id = wp10.meta_key__user_id = 'country'
WHERE wp1.meta_key = 'first_name'

Have you try to make joins with both constants: `ON wp7.user_id = 'first_name' and wp7.meta_key = 'city'`? Also, you have user_id indexed? — dani herrera, Oct 21 '12 at 20:43
Use the MySQL Explain command - http://dev.mysql.com/doc/refman/5.0/en/explain.html - this will point you towards where the query is taking all of it's time, as well as identifying suitable columns for indexing. There are other potential ways of doing your query too - see http://stackoverflow.com/questions/7674786/mysql-pivot-table for example. — dash, Oct 21 '12 at 20:43
Thanks. I'' check out the Explain command and also try joins with both constants. — user1763723, Oct 21 '12 at 20:45
http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png , `wp_usermeta` at the right center. — biziclop, Oct 21 '12 at 20:47
@biziclop: That is only the columns and datatypes. But not the engine and the indexes. — ypercubeᵀᴹ, Oct 21 '12 at 20:49
I edited the question with the default CREATE TABLE statement. — biziclop, Oct 21 '12 at 20:51
Output of SHOW CREATE TABLE wp99_usermeta CREATE TABLE `wp99_usermeta` ( `umeta_id` bigint(20) unsigned NOT NULL auto_increment, `user_id` bigint(20) unsigned NOT NULL default '0', `meta_key` varchar(255) default NULL, `meta_value` longtext, PRIMARY KEY (`umeta_id`), KEY `user_id` (`user_id`), KEY `meta_key` (`meta_key`) ) ENGINE=MyISAM AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 — user1763723, Oct 21 '12 at 21:01
`ENGINE=***` ? I've never seen this engine before... And how many rows does the table have? Only 15? — ypercubeᵀᴹ, Oct 21 '12 at 21:01
Stupid idea, but maybe you could change `FROM wp1 .... WHERE wp1.meta_key='first_name'` to `FROM (SELECT * FROM wp1 WHERE wp1.meta_key='first_name'`) AS wp1 JOIN ...` @ypercube: I edited out the engine because it should be MyISAM, but somewhy it became InnoDB at a test WP install for unknown reason. @user1763723: You can edit your own question, even if somebody has to accept your own edits in the beginning :) — biziclop, Oct 21 '12 at 21:03
@ypercube: no, I just /\ssraped OP's question with some WP database info, because default table structures are always the same. — biziclop, Oct 21 '12 at 21:06
The problem here is that this is a horrible, horrible database design, which cannot do anything except punish anyone who dares to query it. This is even worse in a database which is weak at joins, like MySQL. — Tom Anderson, Oct 21 '12 at 21:09
@biziclop: thanks for the suggestion but I'm not sure I understood you. Are you saying to change the FROM statement to this: "FROM (SELECT * FROM wp1 WHERE wp1.meta_key='first_name')"? That's giving me an error. Can you clarify please? — user1763723, Oct 21 '12 at 21:15
@user1763723: Sorry, that was a stupid idea I think, you should forget it :) — biziclop, Oct 21 '12 at 21:16
@TomAnderson: Why do you say that MySQL is weak at joins? What do you mean? — ypercubeᵀᴹ, Oct 21 '12 at 21:19
ALTER TABLE reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html — biziclop, Oct 21 '12 at 21:36
Thx for ref. I appreciate everyone's help. Off to meeting, back later. Cheers! — user1763723, Oct 21 '12 at 21:38
@ypercube: Traditionally, MySQL's query planner has not been as good at optimising queries involving joins as those of more upmarket databases like PostgreSQL or Oracle. I actually don't know if that's still true; i know MySQL has evolved a lot in the last several years (although so has PostgreSQL!). I also don't know that it's relevant for this query, which certainly has a lot of joins, but might not have any exploitable features that even the best planner could make use of. — Tom Anderson, Oct 21 '12 at 23:15

ypercubeᵀᴹ · Answer 1 · 2012-10-21T21:23:17.237

1

I would add a compound on (meta_key, user_id):

ALTER TABLE wp99_usermeta
    ADD INDEX meta_key__user_id 
      (meta_key, user_id) ;

With the above addition, the existing (meta_key) index is redundant and you can drop it. But @biziclop points the possibility of this interfering with plugins and/or WordPress self-updating (the datatbase) procedures. So, you can leave it and perhaps ask a question about the impact/issues of adding/dropping indexes at WordPress developers forum.

edited Oct 21 '12 at 21:23

answered Oct 21 '12 at 21:08

ypercubeᵀᴹ

113,259
19
174
235

I don't think dropping indexes is a good idea, because who knows how WordPress and its many plugins uses the database. On the other hand adding indexes to a WP table may or may not interfere with WP's self-update mechanism, which sometimes updates database tables too. But it probably won't do anything bad. – biziclop Oct 21 '12 at 21:12
@biziclop; You may have a point there (about dropping indexes). But do you think there are plugins that explicetely try to use an index (with hints?) or add/drop indexes? – ypercubeᵀᴹ Oct 21 '12 at 21:15
That sounds like an interesting idea. Can I leave out the DROP statement and just create the compound index? – user1763723 Oct 21 '12 at 21:17
Thanks @ypercube. I'm not sure I've got it right but posted the latest iteration of code above. Is that close to what you meant? – user1763723 Oct 21 '12 at 21:31
You need to add the index only once. The query does not change (and you shouldn't try to add the index every time you run the query). – ypercubeᵀᴹ Oct 21 '12 at 21:54

score 1 · Answer 2 · answered Oct 21 '12 at 21:20

You could try some fun using GROUP BY:

SELECT
  MAX( IF( meta_key = 'first_name', meta_value , NULL ) AS firstName,
  MAX( IF( meta_key = 'last_name',  meta_value , NULL ) AS lastName,
  ...
FROM wp99_usermeta
GROUP BY user_id

If you have to filter the entities, you can do something similar:

SELECT
  MAX( IF( b.meta_key = 'first_name', b.meta_value , NULL ) AS firstName,
  MAX( IF( b.meta_key = 'last_name',  b.meta_value , NULL ) AS lastName,
  ...
FROM wp99_usermeta a
JOIN wp99_usermeta b
WHERE a.meta_key = 'first_name'
  AND a.meta_value = 'Joe'
GROUP BY user_id

How do I optimize my query?

2 Answers2