Network admin pages don't scale

[ryan]

network/sites.php and network/users.php do queries like:

SELECT COUNT(wp_trunk_users.ID) FROM wp_trunk_users WHERE 1=1

SELECT COUNT( blog_id ) FROM wp_trunk_blogs WHERE site_id = '1'ORDER BY wp_trunk_blogs.blog_id ASC

These are very slow on large networks. get_blog_count() and get_user_count() would mitigate this somewhat, although they too can cause problem on large networks.

Perhaps users.php and sites.php should consult get_user_count() and get_blog_count() and not do a query if the count is over a certain threshold. The tables would default to being empty and a search would have to be performed to get results.

Further, a large network flag may be needed so that functions like get_blog_count() and get_user_count() can avoid ever running COUNT queries and instead rely on the network administrator to perform counts and populate the count site options via a separate job.

[scribu]

Related: #15053

[ryan]

(In [15875]) Update network-wide active user and blog counts via a cron job to avoid costly count queries. see #15170

[jane]

Applying new tag, "iceberg," for tickets that will have multiple commits over time before they are expected to be closed as finished.

[jane]

Who am I kidding? The (tip of the) iceberg tag sounds cool and funny to those of us in here all the time, but new contributors will be confused. New tag will be ongoing-project.

[ryan]

Show latest x sites with no paging if on a large network

[ryan]

Patch changes sites.php to show the latest x registered sites without paging to avoid expensive COUNT queries. I wanted to show nothing so we wouldn't need to do a query that is probably throw away, but showing nothing results in the table markup not being output which will break AJAX search requests which expect the table to already be there. So, this is a compromise.

[ryan]

Screenshot

[ryan]

(In [16137]) For large networks, show the latest registered blogs without paging to avoid expensive count queries. see #15170

[ryan]

$query .= " AND ( {$wpdb->blogs}.domain LIKE '%{$like_s}%' OR {$wpdb->blogs}.path LIKE '%{$like_s}%' ) ";

That is a killer query on large sites. We should probably lose the %s and saerch for $like_s.domain for subdomain installs and domain/$like_s for subdir installs.

[ryan]

(In [16157]) Default to id since registered doesn't have an index. see #15170

[ryan]

(In [16160]) Show latest x users with no paging if on a large network. see #15170

[ryan]

(In [16161]) Lose wildcards in site search. Maybe bring it back as an option. see #15170

[ryan]

(In [16166]) Drop Sites search action dropdown. Determine the search action from the search string. see #15170

[ryan]

(In [16167]) Make IP query threshold more restrictive. see #15170

[ryan]

(In [16169]) Allow trailing wildcard site searches by appending *. see #15170

[ryan]

(In [16170]) Allow trailing wildcard user searches by appending *. see #15170

[TobiasBg]

$wild = 'true';

should be

$wild = true;

for data type consistency, in line 451 of wp-includes/user.php in [16170].

[ryan]

(In [16181]) Use bool not string. Props TobiasBg. see #15170

[scribu]

[16170] broke comment searches.

[ryan]

(In [16262]) Drop display_name from user search columns. It doesn't have an index. see #15170

[nacin]

Replying to scribu:

[16170] broke comment searches.

Sounds like we should override get_search_sql() in WP_User_Query. Nearing the point where WP_Object_Query can probably be killed off.

[ocean90]

See also #15414

[scribu]

(In [16351]) Split get_search_sql(). See #15170. See #15032

[ryan]

(In [16489]) Site search performance tweaks. Use subquery for registration log queries to allow cross DB queries. Lose order by id by default. Search only by blog_id for numeric search strings. see #15170

[ryan]

That loses some flexibility but greatly improves performance. WIll look at a better fix later.

[designsimply]

Trailing wildcard searches were added in [16170]. Could we also allow wildcard user searches such as *gmail.com?

[designsimply]

or *@gmail.com even... @ocean90 ;)

[PeteMall]

Also need to update contextual help for this.