Setting Up for Fuzzy Search

Let's connect to Manticore and create a table with sample data.

mysql -P9306 -h0

Create a table for products with full-text search enabled:

CREATE TABLE products(title text, description text, category string) min_infix_len='2' dict='keywords';

Insert sample product data:

INSERT INTO products(title, description, category) VALUES
('Wireless Bluetooth Headphones', 'Premium quality wireless headphones with noise cancellation', 'Electronics'),
('Mechanical Keyboard', 'RGB backlit mechanical keyboard for gaming', 'Electronics'),
('Wireless Keyboard Combo', 'Wireless keyboard and mouse combo for office work', 'Electronics'),
('Running Shoes', 'Lightweight running shoes for marathon training', 'Sports'),
('Coffee Machine', 'Automatic espresso coffee machine with milk frother', 'Appliances'),
('Coffee Grinder', 'Burr coffee grinder for fresh ground coffee beans', 'Appliances'),
('Toffee Candy Box', 'Assorted toffee candy gift box with 24 pieces', 'Food'),
('Smartphone Case', 'Protective smartphone case with card holder', 'Accessories'),
('Digital Camera', 'Professional digital camera with 4K video recording', 'Electronics'),
('Yoga Mat', 'Non-slip yoga mat for home workouts', 'Sports'),
('Electric Toothbrush', 'Sonic electric toothbrush with multiple modes', 'Personal Care'),
('Laptop Stand', 'Adjustable aluminum laptop stand for desk', 'Accessories'),
('Keyword Research Tool', 'SEO keyword research and analysis tool for marketers', 'Software'),
('Protein Powder', 'Whey protein powder for muscle building', 'Health');

Verify the data:

SELECT * FROM products;

Basic Fuzzy Search

Now let's see how fuzzy search handles typos. First, try a regular search with a misspelled word:

SELECT * FROM products WHERE MATCH('headhpones');

No results! The typo "headhpones" instead of "headphones" breaks the search.

Enable fuzzy search with the fuzzy option:

SELECT * FROM products WHERE MATCH('headhpones') OPTION fuzzy=1;

Now it finds the headphones product despite the typo.

Let's try more examples with common typos:

SELECT * FROM products WHERE MATCH('keybord') OPTION fuzzy=1;

SELECT * FROM products WHERE MATCH('camra') OPTION fuzzy=1;

SELECT * FROM products WHERE MATCH('smartfone') OPTION fuzzy=1;

Fuzzy search uses Levenshtein distance to measure how different the typed word is from indexed words. By default, words with 2 or fewer character differences are matched.

Fuzzy search returns the same results even with a typo. Compare:

SELECT *, WEIGHT() as w FROM products WHERE MATCH('keyboard') OPTION fuzzy=1;

SELECT *, WEIGHT() as w FROM products WHERE MATCH('keybord') OPTION fuzzy=1;

Controlling Fuzzy Distance

The distance parameter controls how many character edits are allowed. Default is 2.

Search with distance=1 (strict - only 1 typo allowed):

SELECT * FROM products WHERE MATCH('headphnes') OPTION fuzzy=1, distance=1;

This finds "headphones" because only one letter is missing.

Try with a word that has 2 errors:

SELECT * FROM products WHERE MATCH('hedphnes') OPTION fuzzy=1, distance=1;

No match with distance=1. Now increase to distance=2:

SELECT * FROM products WHERE MATCH('hedphnes') OPTION fuzzy=1, distance=2;

Now it matches because 2 character differences are allowed.

You can increase distance for more tolerance (but may get less relevant results):

SELECT * FROM products WHERE MATCH('keybrd') OPTION fuzzy=1, distance=3;

Compare the results with different distance values:

SELECT *, WEIGHT() as w FROM products WHERE MATCH('cofee') OPTION fuzzy=1, distance=1;

With distance=1, only "coffee" products match (1 edit: missing 'f').

SELECT *, WEIGHT() as w FROM products WHERE MATCH('cofee') OPTION fuzzy=1, distance=2;

With distance=2, "toffee" also matches (2 edits: c→t, missing 'f'). More tolerance means more results but potentially less relevant ones.

Spell Suggestions with CALL SUGGEST

CALL SUGGEST and CALL QSUGGEST provide spelling suggestions from your index dictionary.

First, enable infixing for suggestions to work. Create a new table:

CREATE TABLE articles(title text, content text) min_infix_len='2' dict='keywords';

Insert some content:

INSERT INTO articles(title, content) VALUES
('Introduction to Programming', 'Learn programming basics with Python and JavaScript tutorials'),
('Advanced Programmer Guide', 'Tips for every programmer to write better programs and clean code'),
('Programmatic SEO Strategy', 'How to use programmatic approaches for automated content creation'),
('Database Management', 'Understanding database design and SQL optimization techniques'),
('Working with Datasets', 'How to manage large datasets and dataframes in data science projects'),
('Machine Learning Guide', 'Comprehensive guide to machine learning algorithms and neural networks'),
('Web Development Tips', 'Modern web development practices using React and Node.js'),
('Cloud Computing Overview', 'Introduction to cloud services AWS Azure and Google Cloud'),
('Comparison of SQL Databases', 'Comparing popular SQL databases including MySQL PostgreSQL and SQLite');

Use CALL SUGGEST to get spelling suggestions for a misspelled word:

CALL SUGGEST('programing', 'articles');

This returns suggestions with Levenshtein distance and document count.

CALL QSUGGEST works on the last word in a phrase (useful for autocomplete):

CALL QSUGGEST('learn programing', 'articles');

Customize suggestion results:

CALL SUGGEST('databse', 'articles', 5 as limit, 2 as max_edits);

Parameters:

• limit: maximum suggestions to return
• max_edits: maximum Levenshtein distance allowed

Practical Fuzzy Search Patterns

Let's combine fuzzy search with other features for real-world applications.

Multi-word fuzzy search:

SELECT * FROM products WHERE MATCH('wireles headfones') OPTION fuzzy=1;

Fuzzy search with filters:

SELECT * FROM products WHERE MATCH('cofee') OPTION fuzzy=1;

Without a filter, this also returns "Toffee Candy Box" (distance=2). Add a category filter to narrow results:

SELECT * FROM products WHERE MATCH('cofee') AND category='Appliances' OPTION fuzzy=1;

Fuzzy search with highlighting (showing what matched):

SELECT HIGHLIGHT() FROM products WHERE MATCH('keybord') OPTION fuzzy=1;

Keyboard layout correction with layouts parameter:

SELECT * FROM products WHERE MATCH('лфзещз') OPTION fuzzy=1, layouts='ru,us';

This helps when users accidentally type in wrong keyboard layout.

Create a search query that handles typos gracefully:

SELECT id, title, WEIGHT() as relevance FROM products WHERE MATCH('keybord') ORDER BY relevance DESC LIMIT 5 OPTION fuzzy=1;

This returns multiple results: "Keyword Research Tool", "Mechanical Keyboard", and "Wireless Keyboard Combo" — sorted by relevance with exact substring matches ranking higher.

Combine fuzzy with multiple words:

SELECT id, title, WEIGHT() as relevance FROM products WHERE MATCH('camra digital') ORDER BY relevance DESC LIMIT 5 OPTION fuzzy=1, distance=2;

Best practices for fuzzy search:

• Start with distance=2 for most use cases
• Use lower distance (1) for short words to avoid false matches
• Combine with CALL SUGGEST to show "Did you mean?" suggestions
• Always sort by relevance (WEIGHT()) to show best matches first