SQL：全文索引以提高速度
超过一千万行，速度慢得令人痛苦。目前使用“LIKE”来计算点击次数：
SELECT COUNT(*) FROM `table` WHERE `job` LIKE '%sales%' AND `location` LIKE '%New York%'
我是否正确地认为我想要：
（1）将全文索引应用于每个字段
（2）使用 CONTAINS 语句来提高速度：
SELECT COUNT(*) FROM `table` WHERE contains('location', '"New York"') and contains ('job',"sales")
这会产生与我最初的结果相同的结果吗？喜欢查询吗？
这是否会快很多倍，唯一的缺点是数据库存储大小的巨大增加？
示例：我想统计这样的数据：“销售经理”，“纽约州布法罗”
More than ten million rows, painfully slow. Currently using 'LIKE' to count the number of hits:
SELECT COUNT(*) FROM `table` WHERE `job` LIKE '%sales%' AND `location` LIKE '%New York%'
Am I correct in thinking that I want to:
(1) Apply FULLTEXT index to each field
(2) Use a CONTAINS statement to increase speed:
SELECT COUNT(*) FROM `table` WHERE contains('location', '"New York"') and contains ('job',"sales")
Would this produce identical results to my initial LIKE query?
Would this be many times faster, with the only downside being a huge increase in the storage size of the database?
Example: I want to count data like this: "sales manager", "Buffalo, New York"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将此索引添加到您的表中：
使用此
WHERE子句：它将工作得非常快。
更多
如果您有很多包含“文本”的列，并且搜索可能包含其中任何一个或全部的内容，那么最好添加一个包含所有内容的额外列文本混在一起。然后在该列上建立一个
FULLTEXT索引，并针对该列进行MATCH索引。请注意，
FULLTEXT不处理数字或不等式。它也不处理“短”单词或“停止”单词。Add this index to your table:
The use this
WHEREclause:It will work immensely faster.
More
If you have lots of columns with "text", and the search may include stuff from any or all of them, it may be better to add an extra column with all the text lumped together. Then have a
FULLTEXTindex on just that column andMATCHagainst just that column.Be aware that
FULLTEXTdoes not handles numbers or inequalities. Nor does it handle "short" words or "stop "words.问：我想将 FULLTEXT 索引应用于每个字段并用类似的内容替换查询以提高速度，这种想法是否正确？
答：是的，您添加索引以提高性能的做法是正确的。
问：我是否认为这会产生与我的 LIKE 查询相同的结果？
答：我不确定，因为我在 MySQL 中找不到任何与 CONTAINS 相关的子句。但请参考此作为解决方案全文索引。
问：我是否正确地认为这会快很多倍，唯一的缺点是数据库存储大小的巨大增加？
A：嗯，老实说，这不是一个好主意。因为文本是不可预测的，所以使用文本作为索引并不是一个好主意。
不管我的想法如何，你可能已经选择了你的想法。但我希望您找到另一篇专栏来降低索引的风险和成本。
谢谢。
Q: Am I correct in thinking that I want to apply FULLTEXT index to each field and replace the query with something like this to increase speed?
A: Yes, you're correct at adding index to make improve the performance.
Q: Am I correct in thinking that this would produce identical results to my LIKE query?
A: I'm not sure cause I couldn't find any clause related to CONTAINS in MySQL. But please refer this as a solution for full-text indexs.
Q: Am I correct in thinking that this would be many times faster, with the only downside being a huge increase in the storage size of the database?
A: Well, honestly speaking, it is not a good idea. Because text is unpredictable, it is not a good idea using text as index.
You might choose already your mind regardless of my thinking. But I hope you find another column for lower risk and lower cost to indexing.
Thanks.