Q:

Paginating a large user database with joins

I have a wordpress user database table 100,000+ users. As part of a plugin I need to list the subscribers. Obviously getting 100,000 users needs to be paginated. To get the total number of users to work out the pagination, I am running the main query without a limit and doing a PHP count() on the results:
SELECT role.umeta_id, role.user_id, role.meta_key, role.meta_value role,       u.ID, u.user_login, u.user_email, u.user_registered
FROM wp_users AS u
LEFT JOIN wp_usermeta role ON role.user_id = u.ID
AND role.meta_key =  'wp_capabilities'
WHERE role.meta_value LIKE  '%subscriber%'
GROUP BY u.ID
ORDER BY u.ID ASC 

I am (unsurprisingly) running out of memory doing this. I have tried just doing a count similar to
SELECT COUNT( u.ID ) 
FROM wp_users AS u
LEFT JOIN wp_usermeta role ON role.user_id = u.ID
AND role.meta_key =  'wp_capabilities'
WHERE role.meta_value LIKE  '%subscriber%'
GROUP BY u.ID
ORDER BY u.ID ASC 

but rather than returning a single value, this returns rows and rows of count = 1.
I know that there are get_user functions in Wordpress to do this - I am just using this as a simplified example (the query is actually more complex)
So the question is "How can I efficiently get the total number of rows in such a situation as this?"

A:

The problem with your query is that you're grouping by u.ID and count is an aggregate function
Edited:
I suggest getting rid of the group and the order by to where you're left with this
SELECT COUNT( u.ID ) 
FROM wp_users AS u
LEFT JOIN wp_usermeta role ON role.user_id = u.ID
AND role.meta_key =  'wp_capabilities'
WHERE role.meta_value LIKE  '%subscriber%'

