SQL · Theory

Query Expansion Fulltext Search

Theory

101/439

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.

Exam tip

MATCH (col) AGAINST ('words') needs a FULLTEXT index.

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.

Short notes

DefFULLTEXT + MATCH … AGAINST for word search.
RuleIndex those columns. InnoDB supported.
TrapLIKE '%word%' instead of MATCH when you already have FULLTEXT.

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

Query Expansion Fulltext Search — sample query

Short notes

Questions

Query Expansion Fulltext Search

Query Expansion Fulltext Search — sample query

Short notes

Questions