Full-text Search, or FTS, is one of the techniques employed by search engines to find results in their database(s). You too can harness the power of FTS to search for patterns that are too complex for the Like operator. In today's blog, we'll learn how full-text searching is implemented in MySQL. In part 2, we'll try our hand at some queries using Navicat for MySQL as our database client.
Full-text Searching Explained
The purpose of FTS is to fetch documents that only loosely match search criteria against textual data. Hence, searching for "cars and trucks", would return results which contain the words separately, as in just "cars" or "trucks", or that contain the words in a different order ("trucks and cars"), or contain variants of the search terms, e.g. "car" and "truck". This allows businesses to guess at what the user is searching for and return more relevant results in a faster time.
Database Management Systems (DBMS) like MySQL do allow quasi text lookups using LIKE expressions. There are however some drawbacks to the Like clause:
- They tend to under-perform on large datasets.
- They're also limited to matching the user's input exactly, which means a query might yield no results even if there are in fact records with relevant information.
Full-Text Searching in MySQL
In order to perform full-text searches in MySQL, you have to add a FULLTEXT index to fields that will support full-text searching. Moreover, full-text indexes can be used only with MyISAM and InnoDB tables. Finally, note that full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns. A FULLTEXT index definition can be given either in the CREATE TABLE statement, or added later using the ALTER TABLE or CREATE INDEX commands. A tip for large data sets: it's much faster to load your data into a table that does not have a FULLTEXT index and then create the index after the data has been loaded, rather than create the FULLTEXT index first and then load the data.
There are three distinct types of full-text searches:
- Natural Language Full-Text Searches
- Boolean Full-Text searches
- Query expansion searches
We'll cover each of these in turn as we go through the list in part 2.
A Basic Example
In the Sakila Sample Database, the film table contains information about each movie in the store's film collection, including its title, running time, and description. We can take a look at a film table record in detail using the Form View. Available in Full Version, the Form View allows us to view, update, insert, or delete data as a form, where the current record is displayed in full detail. There's also navigation bar for switching between records quickly.
We can add full-text searching capability on the description column by adding the FULLTEXT index to it. We could issue either the ALTER TABLE or CREATE INDEX commands, but it Navicat, there's an easier way! The Table Designer contains a number of tabs pertaining to different table objects. These include, column definitions, indexes, foreign keys, triggers, options, and more. We can add a full-text index on the description field by selecting it using the Field Selector dialog, and the choosing FULLTEXT from the Index Type drop-down:
Be sure to leave the Index method blank.
Click the Save button to create the new index:
Conclusion
Now that we've prepped the database for full-text searching, we'll learn how to use Full-Text Search Functions in part 2.
Interested in Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes.
