Like query performance on medium table (~3m records)

Question

I have a small problem. I have a table of about 3m cities, and I need to run a like query on it.

The thing is, it takes about 9s to complete the query. Any ideas how I could make it very fast?

The query is:

SELECT * FROM `cities` WHERE name LIKE '%test%'

possible duplicate of http://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns — Gaurava Agarwal, Jun 28 '16 at 07:55
possibly put the query words you're searching in `like` in another column, make an index for the column, and search by comparing directly your word the new column without the use of `like` — Fredster, Jun 28 '16 at 07:55

score 2 · Accepted Answer · answered Jun 28 '16 at 08:47

try to use a FULLTEXT INDEX. InnoDB now also has FULLTEXT INDEX.

CREATE FULLTEXT INDEX idx_name_ft ON cities  (`name`);

and use like this:

SELECT * FROM cities WHERE MATCH(`name`) AGAINST('test');

sample

i have create a easy sample table with 5000000 row and this it my result:

MariaDB [yourschema]> SELECT * FROM cities WHERE `name` LIKE '%test%';
+---------+------------------------------------------------------+
| id      | name                                                 |
+---------+------------------------------------------------------+
|       7 | name of the city is test more text here              |
|    1096 | name of the city is other more text here - test      |
|    1109 | test name of the city is other more text here        |
| 4998932 | name of the city is other more text here - last test |
| 4999699 | name of the city is other more text here - test some |
| 4999997 | name of the city is - test again - more text here    |
+---------+------------------------------------------------------+
6 rows in set (4.29 sec)

MariaDB [yourschema]> SELECT * FROM cities WHERE MATCH(`name`) AGAINST('test');
+---------+------------------------------------------------------+
| id      | name                                                 |
+---------+------------------------------------------------------+
|       7 | name of the city is test more text here              |
|    1096 | name of the city is other more text here - test      |
|    1109 | test name of the city is other more text here        |
| 4998932 | name of the city is other more text here - last test |
| 4999699 | name of the city is other more text here - test some |
| 4999997 | name of the city is - test again - more text here    |
+---------+------------------------------------------------------+
6 rows in set (0.60 sec)

MariaDB [yourschema]>

Like query performance on medium table (~3m records)

1 Answers1