Make WordPress Core

Opened 4 months ago

Last modified 3 months ago

#51769 new enhancement

Full-text search support

Reported by: zieladam Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Database Keywords: dev-feedback has-patch
Focuses: Cc:

Description (last modified by zieladam)

Let's talk about first-class fulltext search support in WP core. Is there any reason not to support FULLTEXT search in WordPress core for newer MySQL versions with the current search mechanism serving as a fallback for older MySQL versions?

MySQL supports FULLTEXT indexes for MyISAM tables since version 3.23.23 and for InnoDB tables since 5.6.

As @TimothyBlynJacobs noted, WordPress supports versions back to MySQL 5.0:


As @desrosj noted:

  • The recommended versions are MySQL >= 5.6/MariaDB >= 10.1 as per https://wordpress.org/download/.
  • According to the WordPress stats page, a considerable amount of sites use MySQL 5.5 (35%), and some still use 5.1/5,0. Raising support to >= 5.6 is not really an option.

The last discussion about that feature I found was from 10 years ago so maybe it's good time to re-evaluate:


Change History (10)

#1 @zieladam
4 months ago

  • Description modified (diff)

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

4 months ago

#3 @johnbillion
4 months ago

  • Component changed from General to Database
  • Keywords dev-feedback added

#4 @zieladam
4 months ago

  • Description modified (diff)

#5 @zieladam
4 months ago

  • Description modified (diff)

This ticket was mentioned in PR #770 on WordPress/wordpress-develop by adamziel.

3 months ago

  • Keywords has-patch added

This PR proposes core support for fulltext search with fallback to LIKE when fulltext search isn't available.

Sites that would like to keep using LIKE could disable the fulltext_search_enabled option:

<img width="768" alt="Zrzut ekranu 2020-11-26 o 17 12 46" src="https://user-images.githubusercontent.com/205419/100373190-ae7a7800-300a-11eb-9429-bd4545ee7b82.png">

Trac ticket: https://core.trac.wordpress.org/ticket/51769

#7 @zieladam
3 months ago

I came up with a very simple PR that attempts to create FULLTEXT indexes and use them for search later on. If ALTER TABLE ADD FULLTEXT INDEX fails, it simply keeps using the existing LIKE-based code. I would love to get some feedback and discussion here :-) CC @noisysocks @azaozz @TimothyBlynJacobs @andraganescu @desrosj

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

3 months ago

#9 @zieladam
3 months ago

@azaozz raised a great concern about the performance of these queries:

$wpdb->query( "ALTER TABLE $wpdb->posts ADD FULLTEXT INDEX `wp_posts_fulltext_title` (`post_title` ASC);" );
$wpdb->query( "ALTER TABLE $wpdb->posts ADD FULLTEXT INDEX `wp_posts_fulltext_excerpt` (`post_excerpt` ASC);" );
$wpdb->query( "ALTER TABLE $wpdb->posts ADD FULLTEXT INDEX `wp_posts_fulltext_content` (`post_content` ASC);" );

Indeed that could easily time out when there are many posts in the database. Still, there are many sites where that would not be a problem.

We brainstormed a solution that would offer users the best sensible defaults:

  • WP upgrade flow would attempt to create these indexes one time only. Perhaps we could reduce the number of people experiencing these timeouts by short-circuiting if SELECT COUNT(*) from wp_posts is a large number (how large?). Perhaps also short-circuit on multisites. Maybe we could also rely on some filters or otherwise provide a way out for those sites that really don't want to be upgraded.
  • If the indexes are successfully created, that's great and full-text search becomes available on the site. It could be enabled/disabled via site settings.
  • If the operation times out or otherwise fails, it will not be attempted again. Refreshed upgrade screen would include a short note about how Full-text search is available in WP 5.7 but could not be automatically enabled, and then link to a documentation page with further details. The same note would be displayed instead of the related site setting.

In this scenario, the upgrade would be automatic and the failure would handled gracefully.

Version 2, edited 3 months ago by zieladam (previous) (next) (diff)

#10 @peterwilsoncc
3 months ago

Should this go ahead, what would the effect be for someone moving hosts and doing an SQL export from a version that supports full text indexes and attempting to import in to a version that does not?

I can't find a reference but I have seen such a scenario considered in the past however I can't remember the outcome of the discussion.

I'm unsure about the 'large site' check, the logic is sound but in my experience such sites usually use the cli (wp core update-db) for DB upgrades rather than doing them in the admin.

Note: See TracTickets for help on using tickets.