Here are a few options for an engaging question-based title, emphasizing the problem and solution: * **How to Optimize MySQL LIKE \'%string%\' Queries in InnoDB?** (Direct and straightforward) * **In

Optimizing MySQL LIKE '%string%' Queries in InnoDB

In a scenario with a table containing a keywords column and an InnoDB storage engine, optimizing queries using the LIKE '%string%' pattern can be a challenge.

Problem Description

Initially, a simple index was added to the keywords column. However, EXPLAIN queries revealed that the entire table had to be scanned when queries used the '%string%' pattern. This index proved beneficial only for LIKE 'string%' queries.

Index Considerations

For InnoDB tables, fulltext indexes are preferred for queries with floating search terms, where the search term is located anywhere in the string. However, it was previously believed that InnoDB did not support fulltext indexes.

Solution

In recent releases (5.6.4 and later), InnoDB gained fulltext index support. This means that for optimized LIKE '%string%' queries, upgrading to InnoDB version 5.6.4 or higher is recommended. Fulltext indexes are specifically designed for floating search terms.

Benefits of Fulltext Indexes

By using fulltext indexes, 'floating' search terms can be efficiently located within the keywords column. This eliminates the need to scan the entire table, significantly improving query performance for LIKE '%string%' queries in InnoDB tables.

The above is the detailed content of Here are a few options for an engaging question-based title, emphasizing the problem and solution: * **How to Optimize MySQL LIKE \'%string%\' Queries in InnoDB?** (Direct and straightforward) * **In. For more information, please follow other related articles on the PHP Chinese website!