mysql 喜欢性能提升
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2481528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql like performance boost
提问by user121196
is there anyway to speed up mysql like operator performance if wild card is involved? eg. like '%test%'
如果涉及通配符，是否可以像操作员性能一样加快 mysql 的速度？例如。像'%test%'
回答by Cal
MySQL can use an index if your query looks like foo LIKE 'abc%'or foo LIKE 'abc%def%'. It can use the index for any portion or the string before the first wildcard. If you need to match a word anywhere within a string, you might want to consider using FULLTEXTindexes instead.
如果您的查询看起来像foo LIKE 'abc%'或 ，MySQL 可以使用索引foo LIKE 'abc%def%'。它可以使用任何部分的索引或第一个通配符之前的字符串。如果您需要匹配字符串中任意位置的单词，您可能需要考虑使用FULLTEXT索引。
More detail on indexes: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
有关索引的更多详细信息：http: //dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
Full text search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
全文搜索：http: //dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
回答by Max Shawabkeh
Well, the simplest optimization would be to have a constant prefix (e.g. "test%"instead of "%test"or "%test%") since that would allow the engine to narrow down the search space using indices. However, that is not possible in all situations.
嗯，最简单的优化是使用常量前缀（例如"test%"代替"%test"or "%test%"），因为这将允许引擎使用索引缩小搜索空间。然而，这并非在所有情况下都是可行的。
Sometimes a preprocessing step can turn a wildcard search into an ordinary equality or a fulltext search. You'll gain more by finding a way to structure your data so that it does not require a wildcard search than trying to optimize the latter.
有时，预处理步骤可以将通配符搜索转换为普通的等式或全文搜索。通过找到一种结构化数据的方法，与尝试优化后者相比，它不需要通配符搜索，您将获得更多收益。
