How Can I Speed Up Multi-Column LIKE Queries in MySQL?

Speeding Up LIKE Queries on Multiple Columns in MySQL

When faced with frequent SELECT ... LIKE '%text%' queries on a multi-column MySQL table, it's natural to wonder if indexing can improve performance. Unfortunately, indexes that work by referencing characters starting from the left cannot assist with LIKE '%text%' queries, as the wildcard allows for varying characters preceding the specified text.

Instead of relying on indexes, MySQL recommends utilizing Full Text Search (FTS) for MyISAM tables. FTS indexes words within text columns, enabling efficient searches even when the target text appears anywhere within the column.

Alternative Options for Non-MyISAM Tables

For tables that are not MyISAM-based, you can implement a custom indexing system. Create an index table that stores words alongside their corresponding IDs in the actual table. This approach allows for efficient searching by decomposing the LIKE '%text%' query into individual word searches.

Impact on Disk Usage and Performance

FTS indexes do consume additional disk space compared to traditional indexes. However, inserting and deleting records does not significantly affect the performance of the FTS index.

Update: Full Text Search for InnoDB Tables

Beginning with MySQL 5.6, full text search functionality is also available for InnoDB tables, providing a high-performance search solution that aligns with the requirements of your query.

The above is the detailed content of How Can I Speed Up Multi-Column LIKE Queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!