WordPress.org

Make WordPress Core

Opened 10 years ago

Closed 10 years ago

Last modified 5 years 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 10 years ago.
15170.2.diff (1.7 KB) - added by ryan 10 years ago.
Show latest x sites with no paging if on a large network
network-themes.png (161.5 KB) - added by ryan 10 years ago.
Screenshot

Download all attachments as: .zip

Change History (32)

#1 @ryan
10 years ago

  • Description modified (diff)

#2 @scribu
10 years ago

Related: #15053

@ryan
10 years ago

#3 @ryan
10 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
10 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
10 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
10 years ago

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

#6 @ryan
10 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
10 years ago

Screenshot

#7 @ryan
10 years ago

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

#8 @ryan
10 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
10 years ago

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

#10 @ryan
10 years ago

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

#11 @ryan
10 years ago

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

#12 @ryan
10 years ago

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

#13 @ryan
10 years ago

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

#14 @ryan
10 years ago

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

#15 @ryan
10 years ago

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

#16 @TobiasBg
10 years ago

$wild = 'true';

should be

$wild = true;

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

#17 @ryan
10 years ago

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

#18 follow-up: @scribu
10 years ago

[16170] broke comment searches.

#19 @ryan
10 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
10 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.

#22 @scribu
10 years ago

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

#23 @ryan
10 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
10 years ago

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

#25 @designsimply
10 years ago

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

#26 @designsimply
10 years ago

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

#27 @PeteMall
10 years ago

Also need to update contextual help for this.

#28 @ryan
10 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.


5 years ago

Note: See TracTickets for help on using tickets.