Sorting users by post count

[scribu]

Currently, to enable sorting by post count, there's a JOIN made between the users table and the posts table.

This is bad, because users is a global table, which might be stored in a separate database.

Short-term solution for 3.1 is to disable sorting.

Long-term solution is to avoid the JOIN somehow.

(In [17024]) Disable sorting by post count for now. See #15861

Looks that this ticket may stay here for a longer time. What about enabling sorting for single-db config now, and providing fix for single-db and multi-db later?

like sirzooro said, we could do something now and fix it later. Change sorting to a post-query operation and sort manually. Later on, a patch could be written to replace the post-query operation with something in the query. Then it would always work multi-db.

We could potentially do select post_author from $wpdb->posts where post_type = 'post' and post_status = 'publish' group by post_author order by count(ID) desc, then use that data to sort in PHP.

Replying to nacin:

We could potentially do select post_author from $wpdb->posts where post_type = 'post' and post_status = 'publish' group by post_author order by count(ID) desc, then use that data to sort in PHP.

15861.patch​ is an attempt to implement this.

Turned out tricky to use proper LIMITs for paging, so I guess this patch is not scalable. However it should work when querying not only user IDs, but other fields too (in which case $this->results is an array of objects rather than numbers).

Replying to scribu:

users is a global table, which might be stored in a separate database.

How exactly can we move users table to a separate database? Perhaps we should check if it's in the same database and fall back to JOIN, since it's more simple.

Replying to SergeyBiryukov:

How exactly can we move users table to a separate database?

There are several ways you can make queries to a separate DB: create a new WP_DB instance or use SELECT * other_database.wp_users.* etc.

Also, the HyperDB plugin enables this.

So, it seems the "don't JOIN global tables with non-global tables" rule can be broken in the following case:

if ( !wp_is_large_network( 'users' ) && !defined( 'CUSTOM_USER_TABLE' ) && !file_exists( WP_CONTENT_DIR . '/db.php' ) )