What is MySQL FULLTEXT search?
Why Interviewers Ask This
This tests whether you can apply MySQL / SQL knowledge to real-world scenarios. Interviewers are looking for clarity of thought and evidence that you've encountered this in production code.
Answer
FULLTEXT search in MySQL provides natural language text search capabilities, much more powerful than LIKE for searching text columns. It works on CHAR, VARCHAR, and TEXT columns. Creating a FULLTEXT index: ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);. Querying with MATCH...AGAINST: Natural language mode (default): SELECT *, MATCH(title, body) AGAINST ("database optimization" IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(title, body) AGAINST ("database optimization") ORDER BY score DESC; — returns a relevance score; stop words are ignored; results ranked by relevance. Boolean mode: AGAINST ("+mysql -oracle" IN BOOLEAN MODE) — supports operators: + (must include), - (must exclude), * (wildcard), " " (phrase), ~ (reduce relevance). Query expansion mode: first search, then expand with related terms from the initial results. Minimum word length: MyISAM ignores words shorter than 4 chars (ft_min_word_len), InnoDB ignores shorter than 3 (innodb_ft_min_token_size). For production full-text search needs, consider Elasticsearch or MeiliSearch — they provide better relevance ranking, faceted search, and handling of typos.
Pro Tip
This topic has MySQL / SQL-specific nuances that differ from general programming. Highlighting those nuances in your answer shows expertise rather than generic knowledge.
Previous
What is the slow query log in MySQL?
Next
What is the difference between DATETIME and TIMESTAMP in MySQL?