Query Expansion Fulltext Search — SQL Technical Interview Questions & Answers

Query Expansion Fulltext Search

Query Expansion 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: Query Expansion Fulltext Search — sELECT projects columns; WHERE filters; ORDER BY sorts.

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

Code Examples for Query Expansion Fulltext Search

Query Expansion Fulltext Search — sample query

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Eng', 70000),
  (2, 'Ravi', 'Eng', 65000),
  (3, 'Neha', 'HR', 50000);
SELECT name, salary FROM employees WHERE dept = 'Eng' ORDER BY salary DESC;

Query Expansion Fulltext Search — sELECT projects columns; WHERE filters; ORDER BY sorts.

How to Explain Query Expansion Fulltext Search to an Interviewer

How do you search words in MySQL?

FULLTEXT index MATCH … AGAINST