SQL ‘LIKE BINARY’ any slower than plain ‘LIKE’? – This article will take you through the common SQL errors that you might encounter while working with mysql, sql, sql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
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?
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.
I happen to be using MySQL but I’m interested in the answer for SQL databases in general.
Solution :
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, 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.
For the next person who runs across this – in our relatively small database the query:
SELECT * FROM table_name WHERE field LIKE 'some-field-search-value';
... Result row
Returns 1 row in set (0.00 sec)
Compared to:
SELECT * FROM table_name WHERE field LIKE BINARY 'some-field-search-value';
... Result row
Returns 1 row in set (0.32 sec)
Long story short, at least for our database (MySQL 5.5 / InnoDB) there is a very significant difference in performance between the two lookups.
Apparently though this is a bug in MySQL 5.5: http://bugs.mysql.com/bug.php?id=63563 and in my testing against the same database in MySQL 5.1 the LIKE BINARY query still uses the index (while in 5.5 it does a full table scan.)
A trick: If you don’t want to change the type of your column to binary, try to write your WHERE statement like this:
WHERE field = 'yourstring' AND field LIKE BINARY 'yourstring'
instead of:
WHERE field LIKE BINARY 'yourstring'
Indeed, it will check the first condition very quickly, and try the second one only if the first one is true.
It worked well on my project for this test of equality, and I think you can adapt this to the “starts with” test.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.