是否附加一个“AND `columnname` LIKE '%””损害性能?
简单的问题:将这样的内容添加到查询中是否会损害 mysql 的性能,或者它是否会很快得到优化?
AND `name` LIKE '%'
我这里没有可以测试它的大型数据库。
顺便说一句,我想要这个是因为我希望用户能够将一些参数发送到我的服务器端脚本,然后该脚本获取用户指定的准备好的语句并插入参数。我希望用户在搜索时能够省略参数而不影响性能,而 LIKE
通常相对较慢,对吧?
simple question: Does adding something like this to a query hurt performance in mysql or does it quickly get optimized away?
AND `name` LIKE '%'
I've got no big database here that I could test it on.
By the way, I want this because I want users to be able to send some parameters to my server-side script that then grabs the prepared statement specified by the user and inserts the parameters. I want users to be able to omit parameters when searching without hurting the performance, and LIKE
normally is relatively slow, right?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
LIKE 确实可能很昂贵,具体取决于
name
字段上的索引。您是否尝试过衡量您的查询?另请参见http://dev.mysql.com/doc/refman/5.0 /en/explain.html
然后MySQL会告诉你它是否优化掉了不必要的LIKE子句。
LIKE's can be expensive indeed, depending on the indexes you have on the
name
field. Have you tried to measure your query?See also http://dev.mysql.com/doc/refman/5.0/en/explain.html
Then MySQL will give you indications whether it has optimised the unnecessary LIKE clause away or not.
是的。 LIKE的非常贵。但与大多数事情一样,这取决于数据库的大小。
Yes. LIKE's are very expensive. But as with most things, it depends on the size of your DB.
LIKE
肯定会损害性能。要检查的最重要的事情是您是否拥有正确的索引。正在查询的表上有哪些索引?我怀疑,由于您的
LIKE
实际上并未执行任何操作,因此它只会被优化,但您需要进行测试来确认这一点。LIKE
can definately hurt performance. The most important thing to check is that you have the proper indexes. What indexes do you have on the tables being queried?I suspect that since your
LIKE
isn't actually doing anything it would just be optimized out but you will need to test to confirm this.我同意前面的答案:
LIKE
相当昂贵。如果您的表已正确建立索引,则可以使用LIKE
另外查询一个小的结果集。不过,与
OR
结合使用时,查询将再次变得极其昂贵。此外,仅使用
LIKE
来查询索引的VARCHAR
字段最多只能带来最小的性能提升。I agree with the previous answers:
LIKE
is pretty expensive. If your table is properly indexed, a small result-set can be additionally queried usingLIKE
.In conjunction with
OR
the query is going to be brutally expensive again, though.Also, solely using
LIKE
to query an indexedVARCHAR
field should present minimal performance gain at best.刚刚使用 MyISAM 表对 MySQL 5 进行了测试。
似乎 MySQL 查询引擎优化了诸如 '0'='0' 和 LIKE '%' 之类的东西。
Just tested with MySQL 5 using MyISAM tables.
Seems that MySQL query engine optimizes things like '0'='0' and LIKE '%' away.