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

[Bryan Petty]

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