WordPress.org
Get WordPress

Make WordPress Core

Opened 11 years ago

Closed 11 years ago

#30416 closed enhancement (invalid)

Improving Performance for Site with 100k posts

Reported by: wpseoit's profile WpSEOit Owned by:
Milestone: Priority: normal
Severity: critical Version: 4.0
Component: Query Keywords:
Focuses: performance Cc:

Description (last modified by SergeyBiryukov)

Hi all,
i manage a WordPress site that has about 100k posts.
It is configured under a dedicated Server and the performance are good except when users use the search function.

We have the same problem resolved in this ticket: #10964

but our WordPress is 4.0 version and the patch file attached isn't useful for us.

We monitored Db performance with mytop and we found this query very heavy:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%festival.\\%20ombre%') OR (wp_posts.post_con...

Can you help us to patch this problem and find the best solution for improving performance?

Best regards

WpSEO

Change History (3)

#1 @WpSEOit
11 years ago

  • Focuses multisite performance added
  • Severity changed from normal to critical

#2 @SergeyBiryukov
11 years ago

  • Description modified (diff)

#3 @boonebgorges
11 years ago

  • Focuses multisite removed
  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

Thanks for the report, WpSEOit.

At a glance, it looks like this is a search query (example.com/?s=festival.%20hombre). This kind of LIKE query against a very large data set is going to be fairly slow, especially if your post content is quite large. This is a limitation of MySQL, and there's not much that WordPress itself can do to get around it. See #21688, #7394 for some related discussions.

There are WP plugins that improve search performance in various ways. ​https://wordpress.org/plugins/relevanssi/ is a popular one. Realistically, when your site has amassed a certain amount of content, the only way to get truly performant searching is to use an external appliance that's designed for search: something like Sphinx or ElasticSearch (along with corresponding WP integration plugins: ​https://wordpress.org/plugins/wordpress-sphinx-plugin/, ​https://wordpress.org/plugins/fantastic-elasticsearch/ are two examples).

Note: See TracTickets for help on using tickets.