WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

Last modified 9 months ago

#15170 closed defect (bug) (fixed)

Network admin pages don't scale

Reported by: ryan Owned by: ryan
Milestone: 3.1 Priority: normal
Severity: normal Version: 3.1
Component: Multisite Keywords: ongoing-project
Focuses: Cc:

Description (last modified by 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.

Attachments (3)

15170.diff (3.9 KB) - added by ryan 5 years ago.
15170.2.diff (1.7 KB) - added by ryan 5 years ago.
Show latest x sites with no paging if on a large network
network-themes.png (161.5 KB) - added by ryan 5 years ago.
Screenshot

Download all attachments as: .zip

Change History (32)

#1 @ryan
5 years ago

  • Description modified (diff)

#2 @scribu
5 years ago

Related: #15053

@ryan
5 years ago

#3 @ryan
5 years ago

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

#4 @jane
5 years ago

  • Keywords iceberg added
  • Owner set to ryan
  • Status changed from new to assigned

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

#5 @jane
5 years ago

  • Keywords ongoing-project added; iceberg removed

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
5 years ago

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

#6 @ryan
5 years ago

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
5 years ago

Screenshot

#7 @ryan
5 years ago

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

#8 @ryan
5 years ago

$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.

#9 @ryan
5 years ago

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

#10 @ryan
5 years ago

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

#11 @ryan
5 years ago

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

#12 @ryan
5 years ago

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

#13 @ryan
5 years ago

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

#14 @ryan
5 years ago

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

#15 @ryan
5 years ago

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

#16 @TobiasBg
5 years ago

$wild = 'true';

should be

$wild = true;

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

#17 @ryan
5 years ago

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

#18 follow-up: @scribu
5 years ago

[16170] broke comment searches.

#19 @ryan
5 years ago

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

#20 in reply to: ↑ 18 @nacin
5 years ago

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.

#21 @ocean90
5 years ago

See also #15414

#22 @scribu
5 years ago

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

#23 @ryan
5 years ago

(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

#24 @ryan
5 years ago

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

#25 @designsimply
5 years ago

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

#26 @designsimply
5 years ago

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

#27 @PeteMall
5 years ago

Also need to update contextual help for this.

#28 @ryan
5 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

This ticket was mentioned in Slack in #core by boren. View the logs.


9 months ago

Note: See TracTickets for help on using tickets.