Full-text search support

[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:

​https://github.com/WordPress/wordpress-develop/blob/master/src/wp-includes/version.php
src/wp-includes/version.php

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:

https://core.trac.wordpress.org/ticket/14722

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

[zieladam]

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

[zieladam]

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

<?php
$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. There could also be a wp-cli command to attempt the upgrade.

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

[peterwilsoncc]

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.

[zieladam]

hat 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?

@peterwilsoncc My guess is that the import will fail as FULLTEXT KEY will be part of CREATE TABLE and not a separate ALTER TABLE statement. One thing we could do is provide good documentation on how to handle this case (e.g. drop the index before exporting).

It is also worth noting that it the last major MySQL version that wouldn't support it, 5.5, reached end of life in December 2018. Even MySQL 5.6 is not maintained anymore as of February 2021. The official WP.org requirements page says that WordPress requires MySQL 5.6 or newer: ​https://wordpress.org/about/requirements/

So maybe this is not an issue at all?

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.

We could probably follow the same logic in CLI and render a note or prompt the user to answer a yes / no question.