intro
Ever heard of full-text search in MySQL? For MySQL engineers, full-text search is like a gem in the wilderness as it allows to search through data with fuzzy matching. Read this blog to know everything about it!
Are you a seasoned developer or DBA working with MySQL Server? Then you’ve certainly heard about indexes. And since you’ve heard about indexes, you’ve probably heard about MySQL Full-Text Search indexes, too – in case you didn’t, these are “super-charged” types of SQL indexes available in many database management systems.
These types of indexes allow you to search with “fuzzy matching”. Imagine you want to search for a phrase like “This is a Demo”, but make the words “This” or “Demo” have more or less significance when your query is executing. How do you do that? If you’re using simple indexes, you can’t. That’s where full-text search comes into the rescue!
Full-Text Search in MySQL
MySQL full-text search features allows us to search for values that:
For a full-text search in MySQL Server to be possible, you have to define a FULLTEXT index. In other words, you can define an index of FULLTEXT type as follows:
1
ALTER TABLE `demo_table` ADD **FULLTEXT** INDEX ft_idx(`column`);
Nothing that hard!
Start Your MySQL Full-Text Search Journey
To start your database journey using full-text search indexes, we will use a very powerful SQL client called DbVisualizer - it’s essentially a king of the SQL client world used by the best database engineering teams in the world that lets you navigate the complexity of your database instances.
Let’s start from the beginning. First, connect to your database instance using DbVisualizer – launch the tool, then create a new connection towards your database by observing the top left side and clicking on the database with the “+” sign – this icon lets you create a new database connection:
Select MySQL from the list (there’s loads of database management systems that are supported – even if you’re not using MySQL Server, you will surely find something that works for you):
The rest is pretty self-explanatory: specify the details relevant to your MySQL Server, and you’re good to go! Also, keep in mind that it’s not advisable to run “root” on live systems, we’re using the one you see as a demo playing field:
Once you connect, you will be able to observe the databases and tables in them on the left-hand side. Ours is a demo environment so we’ll create a database with DbVisualizer, but you will probably have a couple of databases there to play with already – select your database and you’re good to go:
Now we will create a table bearing a MySQL full-text index on top of one of its columns(note the warning logged by DbVisualizer – integer display width will be deprecated in the next release of MySQL, so we advise you to practice avoiding specifying display width for them too.) The SQL query we’re running is as follows:
1
CREATE TABLE dbvis_example (
2
`id` INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
3
`email` VARCHAR(221) NOT NULL DEFAULT ‘’,
4
`user_details` VARCHAR(25) DEFAULT NULL,
5
FULLTEXT(user_details)
6
);
See? It’s nothing that complex. Simply specify FULLTEXT as part of your column definition. You’re done.
Want to add a full-text index on an already existing column? Run such a SQL query:
1
ALTER TABLE dbvis_example ADD FULLTEXT INDEX ft_idx(email);
Here you go – we came right back where we started. So what’s so special about full-text indexes?
MySQL Full-Text Index Modes
The special thing related to full-text indexes are its search modes. MySQL supports 3 full-text search modes, which are as follows:
Modes are defined together with a MATCH() AGAINST() clause like so:
1
SELECT * FROM [demo_table]
2
WHERE MATCH(column) AGAINST(“value” IN [mode definition here] MODE);
Suppose we’d want to run a query in the natural language mode against a table “wordpress.” Our SQL query would look like so:
1
SELECT * FROM WordPress
2
WHERE MATCH(message) AGAINST(“demo” IN NATURAL LANGUAGE MODE);
Nothing too complex. It gets interesting from here on out though, because modes are unique to themselves.
NATURAL LANGUAGE Mode
The NATURAL LANGUAGE mode treats the search query as if people would be talking between themselves. No characters have a special meaning – it’s the natural, default, mode. Self-explanatory. The SQL queries in this mode look like so (the “*” is a possible wildcard):
1
SELECT * FROM demo_tbl
2
WHERE MATCH(column) AGAINST ("String[*]" [IN NATURAL LANGUAGE MODE]);
BOOLEAN Mode
The BOOLEAN search mode gives certain characters a special meaning. The characters with a special meaning are as follows:
SQL queries using the boolean search mode look like so (here we search for an exact match by wrapping our string in double quotes):
QUERY EXPANSION Mode
The QUERY EXPANSION mode runs the SQL query differently. It makes the MySQL Server run the query twice – once to search for rows that match a given query, and the second time to add all of the most relevant rows to the result set. It’s also sometimes called a “blind query expansion” mode as it relies on implied knowledge.
SQL queries using the query expansion mode look like so:
When to Use Which Mode?
Now that you know what full-text search modes are, you’re wondering when to use them. Everything’s relatively simple:
Limitations of Full-Text Search in MySQL
As with everything, MySQL full-text search is not perfect. It has its own limitations, these being the following:
If none of these issues bother you, feel free to use full-text indexes on your MySQL Server instance, but do note, that these kinds of indexes are far from perfect in other areas as well.
For example, if you find yourself using MySQL 5.7 (which reaches end of life this October), there’s a bug within MySQL discovered by one of our engineers where if you have a lot of data and you have a column with data containing “@” signs, searches involving a full-text index will crash your server. As of the time of writing, the bug is still private (meaning you cannot see any exact details, but we detailed it above.)
A Bug Concerning Fulltext Indexes in MySQL 5.7
MySQL 5.7 was an interesting beast before it reached its End of Life. You see, in 2015 it was the hottest stuff around – everyone was using it! It didn’t come without its flaws, though – one of those flaws concern full-text indexes. If you:
Your SQL query would never complete. Why? Because the full-text index world within MySQL is a whole another beast in and of itself, and also because iIt’s a bug within MySQL! BUG#104263 is its ID and it was discovered by one of our engineers. Interesting, huh?
These kinds of bugs and other related database anomalies are frequently discussed in the Database Dive YouTube channel that’s being run by one of our engineers, so if you’re curious, do have a look!
Summary
We hope that the bug in full-text indexes didn’t scare you away! MySQL full-text search indexes are a really capable beast that, if tamed properly, can make your database sing within no-time. When they’re defined properly, they allow you to make use of their upsides and can rarely cause trouble.
Consider employing database SQL clients like the one provided by DbVisualizer if you’re embarking on your database journey concerning full-text indexes, read up on blogs like TheTable for more database news, and until next time.
FAQs
What is a MySQL full-text search index?
A MySQL full-text index is such an index that allows you to search for data with “fuzzy matching” by making use of one of its search methods.
When to use a MySQL full-text index?
A MySQL full-text index should be used whenever you feel the need to “improvise” in your search methods – perhaps you’re after searching for data in a more exotic fashion? Full-text indexes have your back.
Why should I use a SQL client when dealingil with MySQL full-text search queries?
SQL clients like the one provided by DbVisualizer help you attain your database performance, availability, security, and capacity goals. In particular, it allows you to run SQL queries within a nice UI, letting you observe your tables like a spreadsheet, letting you explore your table structure from within, and much, much more. Did we mention it’s being used by the most widely known companies in the world? That is why you should trust it for your complex full-text search tasks!
