I spent precious hours trying to use the feature included in MySQL since version 5.6, to eventually discover … InnoDB full-text search the use cases where it works are rather limited As an illustration, let’s create a very basic table: CREATE TABLE document (id int PRIMARY KEY,content longtext NOT NULL,FULLTEXT KEY (content)) The table is filled with . The average length of is 2 kilobytes. 2 million rows content Now, let’s run a simple query: SELECT idFROM documentWHERE MATCH(content) AGAINST ('commercial' IN BOOLEAN MODE)LIMIT 50 It’s . No problem. executed in 50 ms Now, let’s combine filtering on a “normal” column and full-text search in the same query: SELECT idfrom documentWHERE MATCH(content) AGAINST ('commercial' IN BOOLEAN MODE)AND id > 10000000LIMIT 50 It’s , which makes it unusable… executed in 50 seconds Please note this is the same query as above, with only one additional condition ( ). Moreover, the query doesn’t return any rows, because the additional condition excludes all rows (all IDs are below 10,000,000). id > 10000000 It’s because the term “commercial” used in my query is common (~300,000 documents contain the term) and because MySQL is not good at merging indexes. Basically, MySQL uses the full-text index to lookup for the term “commercial”, and then it does a kind of nested join to lookup for the 300,000 rows and check the condition on . The former is very quick, but the latter is extremely slow. Why is it so slow? id It would be really great for MySQL to be able to . This would make the built-in full-text search a lot more useful. In the meantime, I have to find an alternate solution. combine multiple index using an in memory bitmap If you have any idea about how to solve this without switching to another database engine, please reach out to me. Thanks to Carlos Otero Barros for reporting a typo.

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

Don’t Waste Your Time With MySQL Full-Text Search

Too Long; Didn't Read

Nicolas Grilly

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Don’t Waste Your Time With MySQL Full-Text Search

Too Long; Didn't Read

Nicolas Grilly

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES