MySQL/MariaDB: How to improve the execution time of SELECT queries with LIKE condition

Question

I am running SELECT queries with the LIKE condition on a table that has more than 100k rows. The query seems very slow.

My queries look like the following query:

SELECT a, b, c

FROM table

WHERE a LIKE '%some_keyword%'

How can I speed up the execution time of such SELECT queries?

Answer 1

If you are using the wildcard ('%') character on both sides of the search keyword, your query will definitely be slow. Even if you create a normal index on the column, the query's execution time will not improve significantly.

To speed up your query, you can define the FULLTEXT index on the columns that you are going to use for searching the keyword. FULLTEXT index will definitely improve the execution time of your query. If you have the latest MySQL or MariaDB, they have support for full-text indexing and searching. You can check more details of the FULLTEXT index here.

Run the following SQL to add FULLTEXT index:

ALTER TABLE table_name ADD FULLTEXT INDEX my_ft_index (column_names)

Then you can use the following SELECT query instead of SELECT query with LIKE.

SELECT * FROM table_name WHERE MATCH(fulltext_indexed_column_name) AGAINST("your_keyword")

On one of my test databases, I ran the following queries and you can see FULLTEXT search is >10 times faster than search using LIKE. On a large database, the FULLTEXT search should outperform.

SELECT *
FROM `videodb`
WHERE title LIKE '%Leela%';

Query took 0.0105 seconds.
 

SELECT *
FROM `videodb`
WHERE MATCH(title) AGAINST ('Leela')

Query took 0.0009 seconds.