MySQL Tuning, Indexing & Concurrency
5-8 Years (Senior)
Medium
Production Scenario
How do you optimize `LIKE "%search%"` full string searches on tables with millions of rows without elasticsearch?
Real-World Scenario / Incident Description
Product title searches with leading wildcards (`LIKE "%laptop%"`) could not use regular B+Tree indexes.
61 views
•
Updated 1 month ago
MySQL FULLTEXT Index with N-Gram Parser
ALTER TABLE products ADD FULLTEXT INDEX ft_title_desc (title, description) WITH PARSER ngram;
-- High performance full text search
SELECT * FROM products
WHERE MATCH(title, description) AGAINST('+laptop +pro' IN BOOLEAN MODE);
Related Concepts:
#Fulltext Index
#LIKE Search
#Performance
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →