SQL'LIKE BINARY'比普通的'LIKE'慢吗? [英] SQL 'LIKE BINARY' any slower than plain 'LIKE'?

问题描述

我正在使用django应用程序，该应用程序执行了一些"startswith" ORM操作，将longtext列与unicode字符串进行了比较.这将导致使用u'mystring' Unicode字符串进行LIKE BINARY比较操作.像BINARY这样的速度可能会比普通的LIKE慢吗?

I'm using a django application which does some 'startswith' ORM operations comparing longtext columns with a unicode string. This results in a LIKE BINARY comparison operation with a u'mystring' unicode string. Is a LIKE BINARY likely to be any slower than a plain LIKE?

我知道一般的答案是基准测试，但是我想对数据库有一个总体的了解，而不仅仅是我的应用程序，因为我以前从未见过LIKE BINARY查询.

I know the general answer is benchmarking, but I would like to get a general idea for databases in general rather than just my application as I'd never seen a LIKE BINARY query before.

我碰巧正在使用MySQL，但总体上我对SQL数据库的答案很感兴趣.

I happen to be using MySQL but I'm interested in the answer for SQL databases in general.

推荐答案

如果性能似乎成为问题，可能创建第一个eg的副本是一个好主意.长文本的255个字符，在该文本上添加索引，然后在该文本上使用startswith.

If performance seems to become a problem, it might be a good idea to create a copy of the first eg. 255 characters of the longtext, add an index on that and use the startswith with that.

BTW，此页面显示:如果需要进行区分大小写的匹配，请将您的列声明为BINARY；请勿在查询中使用LIKE BINARY来强制转换非二进制列.如果这样做，MySQL将不会在该列上使用任何索引."这是一个古老的提示，但我认为这仍然有效.

BTW, this page says: "if you need to do case-sensitive matching, declare your column as BINARY; don't use LIKE BINARY in your queries to cast a non-binary column. If you do, MySQL won't use any indexes on that column." It's an old tip but I think this is still valid.

这篇关于SQL'LIKE BINARY'比普通的'LIKE'慢吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！