对于具有自动完成功能的机场输入字段,目前有一个包含机场描述的表,autocomplete_airport：
lang | description (with INDEX) | ...
-----+----------------------------------------------------+----
pt | New York - John F Kennedy (JFK), Estados Unidos | ...
pt | Nova Iorque - John F Kennedy (JFK), Estados Unidos | ...
...
自动完成功能适用于单个单词.因此,当用户输入“yor”时,则显示“new york”(如果在LIMIT中).查询目前的工作方式如下：
SELECT * FROM autocomplete_airport WHERE lang = "pt"
AND (description LIKE "%(yor)%"
OR description LIKE "yor%"
OR description LIKE "% yor%")
ORDER BY description
LIMIT 15
现在我想知道如何加快速度.一个想法是使用表autocomplete_airport和autocomplete_airport_word创建以下数据库结构：
id | lang | description (with INDEX) | ...
-----+------+----------------------------------------------------+----
123 | pt | New York - John F Kennedy (JFK), Estados Unidos | ...
124 | pt | Nova Iorque - John F Kennedy (JFK), Estados Unidos | ...
...
word (with INDEX) | autocomplete_airport_id
------------------+------------------------
New | 123
York | 123
John | 123
F | 123
Kennedy | 123
JFK | 123
...
那么SELECT只需要在字符串的开头搜索：
SELECT DISTINCT autocomplete_airport.*
FROM autocomplete_airport
INNER JOIN autocomplete_airport_word
ON autocomplete_airport.id = autocomplete_airport_word.autocomplete_airport_id
WHERE lang = "pt"
AND word LIKE "yor%"
ORDER BY description
LIMIT 15
那个新结构值得吗？它会真的加快速度吗？有更简单的方法吗？
更新
只是注意到单词表有一个缺陷.结果：搜索“纽约”不会给出任何结果.应该做些什么：
term (with INDEX) | autocomplete_airport_id
------------------------------------------------+------------------------
New York - John F Kennedy (JFK), Estados Unidos | 123
York - John F Kennedy (JFK), Estados Unidos | 123
John F Kennedy (JFK), Estados Unidos | 123
F Kennedy (JFK), Estados Unidos | 123
Kennedy (JFK), Estados Unidos | 123
(JFK), Estados Unidos | 123
Estados Unidos | 123
Unidos | 123
JFK | 123
732
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
