Add relevant search results, expand your search to all fields in a database, and run “fuzzy” searches in MySQL.
TL;DR: The bigger and more complicated the database, the more talented its search capabilities need to be. These three techniques let you broaden the results returned by MySQL searches: one via automatic relevance feedback, another by searching all fields in all tables of the database, and a third by finding approximate matches to your search term.
A database doesn’t do users much good if they can’t find the data they’re searching for. The larger and more complex the database elements, the more sophisticated your searches have to be to return the information you need.
In MySQL, full-text indexes apply to CHAR, VARCHAR, or TEXT columns in InnoDB and MyISAM tables. As the MySQL Reference Manual explains, full-text searchers are done using the MATCH() … AGAINST syntax: the former accepts a comma-separated list identifying the columns to be searched; and the latter accepts a string to search for, and optionally a modifier indicating the type of search to be conducted.
The three types of full-text searches are natural language, boolean, and query expansion, which broadens the results of a natural-language search based on automatic relevance feedback (also called blind query expansion). A MySQL Tutorial steps through the process of crafting and applying a query expansion search.
When you add WITH QUERY EXPANSION to the AGAINST() function, the MySQL search engine first finds all rows matching the search query, then it checks those rows for the relevant words, and finally it searches again based on the relevant words rather than the user’s original keywords. The tutorial uses the example of a search of a car database with and without query expansion.
A MySQL full-text search without query expansion (top) and with query expansion (bottom). Source: MySQL Tutorial
Multiple ways to search all tables in MySQL
When a Stack Overflow poster asked how to search all fields in all tables of a MySQL database, several different solutions were offered. The simplest is a SQLDump and then search the database as a file. To make the result easier to read, use –extended-insert=FALSE flag for mysqldump.
Another option is to use the search feature in phpMyAdmin: select the database, choose Search, enter your search term, and select the tables to be searched. A third solution is the following function:
This MySQL function searches all fields in all tables of a MySQL database. Source: Stack Overflow
Use SoundEx to include approximations in search results
Approximate string matching is an algorithmic trick upon which empires have been built. However, the fuzzy searching techniques that are common in Web search engines are lacking in MySQL, at least in terms of a direct equivalent search function. In a March 9, 2015, post, Database Journal’s Rob Gravelle describes how to use the SoundEx phonetic algorithm to perform fuzzy searches in MySQL.
SoundEx reduces text to the sound when spoken. This makes it easy to spot misspellings, which are likely to have the same SoundEx string as the correct spelling. The three arguments the function takes are the needle (the search target), haystack (the text to be searched), and splitChar (usually a space).
Gravelle devises a function he names soundex_match_all that extends SoundEx to search for strings of words.
Use of MySQL’s SoundEx function to search for approximate matches is extended to strings of words by this function. Source: Database Journal
The new Morpheus Virtual Appliance lets you provision, deploy, and monitor heterogeneous MySQL, MongoDB, Redis, and ElasticSearch databases from a single point-and-click console. With the Morpheus database-as-a-service (DBaaS) you can manage all your SQL, NoSQL, and in-memory databases across public, private, and hybrid clouds in just minutes. Each database instance you create includes a free full replica set for built-in fault tolerance and fail over.
In addition, the service allows you to migrate existing databases from a private cloud to the public cloud, or from public to private. A new instance of the same database type is created in the other cloud, and real-time replication keeps the two databases in sync. Visit the Morpheus site for pricing information and to create a free account.
