Fix: pagination broken in sites + users admin pages for large WPMS sites

Since upgrading to WP 3.1 I've had issues with using the network admin "sites" and "users" pages. Quite simply, my sites were too big, so the pagination options disappeared.

Tonight, I had the time and was "encouraged to fix it" for one of my clients. Digging thru the code led me to these two files:


The line that needs to be changed is where it checks for "large" sites. Those with more than 10000 records of each type. The simplest fix for me was to replace 10000 with 100000 on lines 43 and 44, respectively. Pagination immediately started working again. More about why this was changed here:
Yes, there will be a performance hit by increasing the pagination threshold this way (it ought to be a configurable variable), but sometimes you just need to be able to access the data *anyway*.

However, you can help improve performance as an alternative. Currently the indices on the blogs and users tables are not optimized for the SQL that's actually pulling these counts, so it's a long call without an index. Bad idea. Here's the SQL (from ms-functions.php):

"SELECT COUNT(blog_id) as c FROM $wpdb->blogs WHERE site_id = %d AND spam = '0' AND deleted = '0' and archived = '0'"
"SELECT COUNT(ID) as c FROM $wpdb->users WHERE spam = '0' AND deleted = '0'"

So add the following indices to optimize this for these functions:

ALTER TABLE wp_blogs ADD INDEX ( site_id, spam, deleted, archived );
ALTER TABLE wp_users ADD INDEX ( spam, deleted );