我有一个小问题。我有一个约300万个城市的表,我需要在其上运行like
查询。
事实是,完成查询大约需要9秒。有什么想法我如何使它非常快?
查询是:
SELECT * FROM `cities` WHERE name LIKE '%test%'
尝试使用 fullText index 。InnoDB现在还具有完整索引。
CREATE FULLTEXT INDEX idx_name_ft ON cities (`name`);
并这样使用:
SELECT * FROM cities WHERE MATCH(`name`) AGAINST('test');
示例
我创建了一个带有5000000行的简单示例表,这是我的结果:
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]>