What is type fulltext in MySQL EXPLAIN?

The fulltext type in the MySQL EXPLAIN indicates that a full-text search is executed for the table.

TODO more! more! more! filtering with other indexes etc

AI is useless for this

Examples

✅ When it is used

CREATE TABLE logs (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message varchar(1000) NOT NULL,
    FULLTEXT (message)
);

SELECT * FROM logs WHERE MATCH(message) AGAINST('kernel panic');

A full-text index is always used whenever a full-text search is executed on the table - it is impossible to do a full-text search without a full-text index.

How to optimize a query using fulltext?

You can't do much to optimize full-text searches in MySQL. Anytime a full-text search is executed, the full-text index is used and all other indexes are ignored. So you never can persuade MySQL to use any other index you would have hoped to use. The only options you have are:

• Not doing a full-text search or using specialized search databases with more capabilities and better performance
• Splitting your table into multiple smaller ones that will have less data so searching is faster and filtering those results by other columns don't have to throw as many unmatching results

Conclusion

The fulltext type indicates that a full-text index is used for a full-text search. There is nothing you can change on the query or by adding different indexes to get a different result on how this table will be accessed. This behavior is a limitation of MySQL's full-text search implementation, but it is not a problem in most cases. However, further conditions for that table will be executed inefficiently: Every row matching the full-text search will be loaded and then evaluated against those additional conditions. This process will be relatively inefficient if the conditions will discard many rows. Typically, you would create a better index containing the columns for these conditions, but that's impossible with full-text indexes.