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

[Mike Walsh]

I am no SQL guru by any means - as I noted, the SQL came from help on this
mailing list back in 2008.  Since I was unable to get the information I
want (first name and last name) using get_users(), I adapted the SQL you
provided below and it works great - thank you very much.

Mike

On Wed, Jun 20, 2012 at 3:43 PM, Bryan Petty <bryan at ibaku.net> wrote:

> On Wed, Jun 20, 2012 at 6:42 AM, Mike Walsh <mpwalsh8 at gmail.com> wrote:
> > 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.
>
> Sorry, but this is some pretty bad SQL. Let me help though.
>
> The problem here is not that it uses joins, but that it uses joins
> with subqueries, and that it's also a double join with the usermeta
> table, not just the users table (even though no wp_usermeta columns
> are being selected in the outer main query).
>
> If you can, it's recommended that you use Simon's suggested approach
> that does not require custom SQL in your WordPress plugins. However,
> if you have to for one reason or another, here's a much more
> appropriate SQL query:
>
> SELECT ID, display_name, user_email,
>    m1.meta_value first_name, m2.meta_value last_name
> FROM wp_users u
> LEFT JOIN wp_usermeta m1 ON
>    (m1.user_id = u.ID AND m1.meta_key = 'first_name')
> LEFT JOIN wp_usermeta m2 ON
>    (m2.user_id = u.ID AND m2.meta_key = 'last_name')
> WHERE ID <> 1
> ORDER BY last_name
>
> With the proper indexes (in this case, wp_users.ID and
> wp_usermeta.user_id), joins are a very natural part of relational
> databases, and are incredibly efficient as long as they aren't used
> with subqueries. I haven't used a single subquery with this. This
> should run in a fraction of a second even with over 10,000 users,
> possibly even 100,000 users no problem.
>
> Regards,
> Bryan Petty
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>



-- 
Mike Walsh - mpwalsh8 at gmail.com