SQL · Theory

Boolean Fulltext Search

Theory

100/439

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.

Exam tip

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

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.

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 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

Boolean Fulltext Search — sample query

Short notes

Questions

Boolean Fulltext Search

Boolean Fulltext Search — sample query

Short notes

Questions