Is SQL "LIKE BINARY" Slower than Normal "LIKE"?

Question

I am using a django application that does some “startswith” ORM operations by comparing longtext columns with a unicode string. This results in a LIKE BINARY comparison operation with the unicode string u'mystring' . Is LIKE BINARY most likely slower than a regular LIKE?

I know that the general answer is benchmarking, but I would like to get a general idea for the databases in general, and not just for my application, since I had never seen a LIKE BINARY request before.

I use MySQL, but I'm interested in the answer for SQL databases in general.

Answer 1

If performance seems like a problem, it might be a good idea to create a copy of the first, for example. 255 characters long text, add an index to it and use startswith with it.

BTW, this page says : "If you need to make case-sensitive matches, declare your column as BINARY, do not use LIKE BINARY in your queries to create a non-binary column. If you do, MySQL will not use indexes on this column." This is an old tip, but I think it is still relevant.

Answer 2

For the next person who goes through this, in our relatively small database, the request is:

 SELECT * FROM table_name WHERE field LIKE 'some-field-search-value'; ... Result row Returns 1 row in set (0.00 sec) 

Compared with:

 SELECT * FROM table_name WHERE field LIKE BINARY 'some-field-search-value'; ... Result row Returns 1 row in set (0.32 sec) 

In short, at least for our database (MySQL 5.5 / InnoDB) there is a very significant performance difference between the two searches.

Obviously, this is a mistake in MySQL 5.5: http://bugs.mysql.com/bug.php?id=63563 and in my testing on the same database in MySQL 5.1, the LIKE BINARY query still uses the index (while in 5.5, it performs a full table scan.)

Answer 3

Trick: If you do not want to change the type of your column to binary, try writing a WHERE expression like this:

 WHERE field = 'yourstring' AND field LIKE BINARY 'yourstring' 

instead:

 WHERE field LIKE BINARY 'yourstring' 

Indeed, he will very quickly check the first condition and try the second only if the first is true.

This test worked well in my project for the equality test, and I think you can adapt it to the “starts with” test.