MySQL 不使用索引(尽管 FORCE INDEX)

Question

我需要使用 PHP 和 jQuery 进行实时搜索，以从 cities(将近 300 万行)和 countries(几百行)这两个表中选择城市和国家/地区.

有一小段时间我在考虑为 cities 使用 MyISAM 表，因为 InnoDB 不支持 FULLTEXT 搜索，然而决定这不是一个可行的方法(频繁的表崩溃，所有其他表都是 InnoDB 等等，MySQL 5.6+ InnoDB 也开始支持 全文索引)。

所以，现在我仍然使用 MySQL 5.1，并且由于大多数城市只包含一个词或最多 2-3 个词，但是例如“New York”——如果他们指的是“New York”，大多数人不会搜索“York”。所以，我只是在 city_real 列(它是一个 varchar)上放置了一个索引。

以下查询(我在不同的版本中尝试过，没有任何 JOIN 也没有 ORDER BY，有 USE INDEX 甚至有 FORCE INDEX，我尝试用 LIKE 代替 equal (=) 但另一篇文章说 = 更快，如果通配符只在末尾，可以使用它)，在 EXPLAIN 它总是说“使用哪里，使用文件排序”。查询的平均时间约为 4 秒，您必须承认这对于实时搜索(用户在文本框中输入并查看城市和国家/地区的建议)来说有点慢......

如果用户输入至少 3 个字符，实时搜索 (jQuery ajax) 会搜索...

SELECT ci.id, ci.city_real, co.country_name FROM cities ci LEFT JOIN countries co ON(ci.country_id=co.country_id) WHERE city_real='cit%' ORDER BY population DESC LIMIT 5

ci.id 上有一个PRIMARY，ci.city_real 上有一个INDEX。知道为什么 MySQL 不使用索引吗？或者我怎样才能加快查询速度？或者我应该/不应该在其他什么地方设置 INDEX？

非常感谢您的帮助!

---

这是解释输出

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  ci  range   city_real   city_real   768 NULL    1250    Using where; Using filesort
1   SIMPLE  co  eq_ref  PRIMARY PRIMARY 6   fibsi_1.ci.country_id   1    

Answer 1

您应该使用 WHERE city_real LIKE 'cit%'，而不是 WHERE city_real='cit%'。

I have tried LIKE instead equal (=) but another post said = was faster and if the wildcard is only at the end, it is OK to use it

这是错误的。 = 不支持通配符，所以它会给你错误的结果。

Or how I could speed up the query?

确保您在两个表中都有关于 country_id 的索引。如果您需要进一步的帮助，请发布 EXPLAIN SELECT ... 的输出。