Boolean Fulltext Search — SQL Technical Interview Questions & Answers

Boolean Fulltext Search

Boolean Fulltext Search

  • FULLTEXT index lets MATCH (title, body) AGAINST ('exam prep') search words, not LIKE '%exam%'. Needs a FULLTEXT index on those columns (InnoDB in 5.6+).
  • Natural language mode is default. BOOLEAN MODE uses +must -mustnot. Query expansion is a follow-up search — mention only if asked.
  • Trap: FULLTEXT on InnoDB with tiny tables (minimum token length, stopwords). Trap: MATCH on columns that are not in the index together.
  • On the example next to this theory: Boolean Fulltext Search — wHERE combines predicates with AND/OR; LIKE does pattern match.

Technical Interview Tip: MATCH (col) AGAINST ('words') needs a FULLTEXT index.

Code Examples for Boolean Fulltext Search

Boolean Fulltext Search — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

Boolean Fulltext Search — wHERE combines predicates with AND/OR; LIKE does pattern match.

How to Explain Boolean Fulltext Search to an Interviewer

How do you search words in MySQL?

FULLTEXT index MATCH … AGAINST