Skip to:
Content

bbPress.org

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#2972 closed defect (fixed)

Unnecessary use of SQL_CALC_FOUND_ROWS

Reported by: nielsvk Owned by: johnjamesjacoby
Milestone: 2.6 Priority: high
Severity: normal Version: trunk
Component: General - Performance Keywords: needs-patch needs-unit-tests
Cc: espellcaste@…

Description

While testing bbPress 2.6-alpha I noticed some very slow queries when posting a new topic. The queries use SQL_CALC_FOUND_ROWS where it doesn't seem to be needed. This is very inefficient on large tables. By setting the WP_Query parameter 'no_found_rows' to true in the following functions, the time to post a topic in a large forum decreased from ~30s to ~4s:

bbp_get_public_child_last_id
bbp_get_public_child_ids
bbp_forum_query_last_reply_id

There might be more.

Note: this kind of issue is easily found when testing with realistic amounts of data (i.e. comparable to a production situation). I recommend making this a standard part of your development and testing process. For example our database is fairly large: ~2.6 million records in wp_posts, 11 millions records in wp_postmeta. 25 forums, 138K topics, 2.4M replies.
Posting in a large forum was taking >30s, after applying fix ~4s.
As a reference, the same post takes ~12s with bbPress 2.5 so nice improvement in 2.6!

Change History (5)

#1 @johnjamesjacoby
3 years ago

  • Component changed from General to General - Performance
  • Keywords needs-patch needs-unit-tests added
  • Milestone changed from Awaiting Review to 2.6
  • Owner set to johnjamesjacoby
  • Priority changed from normal to high

#2 @espellcaste
3 years ago

  • Cc espellcaste@… added

+1 Interesting improvements!

#3 @netweb
3 years ago

In 6077:

Tests: Add test_bbp_forum_query_last_reply_id()

See #2972

#4 @johnjamesjacoby
3 years ago

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

In 6219:

Queries: Use no_found_rows where relevant.

This prevents SQL_CALC_FOUND_ROWS usages in places where it is not used.

Fixes #2972.

#5 @johnjamesjacoby
3 years ago

In 6221:

Moderators: Update bbp_get_moderator_forum_ids() to use same approach as favs/subs.

See #459, #2959, #2972.

Note: See TracTickets for help on using tickets.