Wordpress管理员用户

Question

I was trying to write an sql query for wordpress but why is my query very slow? it takes 3.5 seconds. im trying to copy the one under wordpress wordpress/wp-admin/users.php which selects users but its fast.

SELECT 
usr.display_name,
m1.meta_value,
m2.meta_value
from wp_users usr
JOIN wp_usermeta m1 ON (m1.user_id = usr.id AND m1.meta_key = 'first_name')
JOIN wp_usermeta m2 ON (m2.user_id = usr.id AND m2.meta_key = 'last_name') 
ORder by usr.user_login
LIMIT 0,30

Answer 1

Using the query as given should be rather fast, since you're ordering on an index on the wp_users table, MySQL is able to locate the specific 30 records that you wish to pull.

MySQL only has to pull 30 records from wp_users, then do two lookups per record for the join (30 * 2 = 60). Unfortunately, each of those lookups require a table scan since the index on wp_usermeta.user_id only get them half way to the particular record they are trying to find. Still, since it's just 30 records (60 lookups), it should be rather fast. One way to improve this would be to add a compound index on wp_usermeta.user_id + wp_usermeta.meta_key, which would avoid the small table scan.

You may have even better success with a covering index, assuming the values are small enough to fit in the index "prefix", by creating a compound index on all three fields: wp_usermeta.user_id + wp_usermeta.meta_kety + wp_usermeta.meta_value.

However, when ordering by lastname, firstname, MySQL must pull all 8,500 records in your wp_users table, do all the lookups for the join (8,500 * 2 = 17,000 lookups) each of which must do a table scan, then order them in a temporary table, before it can locate the 30 records you want.

The solution here might be to create the same covering index as mentioned in the first part. You'd want to do a self-join on the wp_usermeta table and hope that MySQL actually uses its index merge optimization to locate the 30 records you want, before doing a join back to the wp_users table:

SELECT 
usr.display_name,
m1.meta_value,
m2.meta_value
FROM wp_usermeta m1
JOIN wp_usermeta m2
  ON (m1.user_id = m2.user_id AND m1.meta_key = 'lastname' AND m2.meta_key = 'firstname'
JOIN wp_users usr
  ON m1.user_id = usr.id
ORDER BY m1.meta_value, m2.meta_value
LIMIT 0,30

If that doesn't work out for you, then consider ordering only by last name, using a compound index on wp_usermeta.meta_key + wp_usermeta.meta_value.