Solution: Use the Right Tool for the Job

It’s best to use specialized search engine technology instead of SQL. Another way to optimize is to reduce the recurring cost of search by saving the results. The following sections describe some of the technologies offered as built-in extensions by different database brands, as well as technologies offered by independent projects. We’ll also develop a solution that uses standard SQL but is more efficient on average than substring matching.

Vendor extensions

Every major brand of a database has invented its own answer to the common requirement of full-text search, but these features are not standard or compatible between database brands. If we use a single brand (or are willing to use vendor-dependent features), these features are the best way to get high-performance text search, with the greatest integration with SQL queries.

The following are brief descriptions of full-text search features in several brands of SQL databases. The details are subject to change, so we must always read the current documentation for our brand.

Full-text index in MySQL

MySQL provides a simple full-text index type for the MyISAM storage engine. We can define a full-text index over columns of type CHAR, VARCHAR, or TEXT. Here’s an example that defines a full-text index that includes content from the bug summary and description columns: