[wp-hackers] Seeking SQL advice to identify performance problem

[Paul Menard]

On Jun 20, 2012, at 8:42 AM, Mike Walsh wrote:

> SELECT DISTINCT ID, display_name, user_email, user_login, first_name,
> last_name FROM wp_usermeta, wp_users
> LEFT JOIN ( SELECT user_id AS uid, meta_value AS first_name FROM
> wp_usermeta WHERE meta_key = 'first_name' ) AS metaF ON wp_users.ID =
> metaF.uid LEFT JOIN ( SELECT user_id AS uid, meta_value AS last_name
> FROM wp_usermeta WHERE meta_key = 'last_name' ) AS metaL ON
> wp_users.ID = metaL.uid  WHERE (id<>1)  ORDER BY last_name
> 
> In my development area this query takes almost 4 minutes to run and my
> WordPress database has 650 users defined.  There is very little other
> content in the database besides some test posts.


I might just be too old school. I don't have much of a need to write my own SQL any more thanks to WordPress. But when I do I never structure my JOINS like that. I would write the same SQL something like the following. Seems much more straight forward when I read it. I know the JOIN is effectively performing a sub-query which is how your query is written. 

SELECT ID, u.display_name, u.user_email, u.user_login, um_fname.meta_value as first_name, um_lname.meta_value as last_name
FROM wp_users u
LEFT JOIN wp_usermeta um_fname ON u.ID=um_fname.user_id AND um_fname.meta_key='first_name'
LEFT JOIN wp_usermeta um_lname ON u.ID=um_lname.user_id AND um_lname.meta_key='last_name'
WHERE (u.ID<>1)
ORDER BY um_lname.meta_value

P-