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

[Mike Walsh]

On Wed, Jun 20, 2012 at 1:42 PM, Mike Walsh <mpwalsh8 at gmail.com> wrote:

> I have a query that I have used in a couple plugins that someone on this
> mailing list helped me with several years ago.  I want to retrieve the list
> of Users, including their ID, username, display name, first name, and last
> name so I can present the information in a form in a way that is easily
> readable.  This is the query I am using:
>
> 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.
>
> --
> Mike Walsh - mpwalsh8 at gmail.com
>

Inadvertently hit "Send" to quickly!  Is there a better (aka more
efficient) way to retrieve this information?  I am guessing the LEFT JOIN
constructs I am using are really slowing it down.  I have some plugin users
that have more than 3000 users so it is never completing for them.

Thanks,

Mike
-- 
Mike Walsh - mpwalsh8 at gmail.com