提高MySQL LIKE查询速度? [英] Increase speed of MySQL LIKE query?

问题描述

对于具有自动补全功能的机场输入字段，当前存在一个带有机场说明的表，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"时，就会显示纽约"(如果在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

解决方案

正如MartinK所说，如果您的表只有几百行，即使没有优化，您的查询也应该非常快-值得检查正在发生的事情.

但是，搜索文本字段的最佳方法是使用全文本索引(http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html)-这是专门为您正在描述的情况.

For an airport input field with autocompletion, there currently is one table with airport descriptions, autocomplete_airport:

lang | description (with INDEX)                           | ...
-----+----------------------------------------------------+----
pt   | New York - John F Kennedy (JFK), Estados Unidos    | ...
pt   | Nova Iorque - John F Kennedy (JFK), Estados Unidos | ...
...

Autocompletion works on individual words. So when the user enters "yor", then "new york" shows up (if in LIMIT). The query currently works as follows:

SELECT * FROM autocomplete_airport WHERE lang = "pt"
AND (description LIKE "%(yor)%"
     OR description LIKE "yor%"
     OR description LIKE "% yor%")
ORDER BY description
LIMIT 15

Now I wonder how to speed up things. One idea is to create the following database structure, with tables autocomplete_airport and 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
...

Then SELECT would only need to search at the beginning of strings:

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

Is that new structure worth the trouble? Would it really speed up things? Is there a simpler way?

Update

Just noticed that the word table has a flaw. The consequence: Searching for "New York" would not give any result. What should work:

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

解决方案

As MartinK says, if your table has just a few hundred rows, your query should be pretty quick even without optimization - it's worth checking what's going on.

However, the best way to search text fields is to use full text indexing (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) - this is designed for exactly the case you're describing.

这篇关于提高MySQL LIKE查询速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！