mysql binary comparison doesnt use index

binary, indexing, mysql, select, sql

Solution

MySQL uses the collation of the column for the index. An index with a non-binary collation isn't useful for a binary lookup since the order might be different.

You could change the column itself to binary collation:

ALTER TABLE YourTable MODIFY
   YourColumn VARCHAR(4)
   CHARACTER SET latin1
   COLLATE latin1_bin;

Then the index would be useful for a binary lookup.

Problem

I have the following query: ``` EXPLAIN EXTENDED SELECT * FROM ( `photo_data` ) LEFT JOIN `deleted_photos` ON `deleted_photos`.`photo_id` = `photo_data`.`photo_id` WHERE photo_data.photo_id = 'UKNn' AND `deleted_photos`.`photo_id` IS NULL ``` I unfortunately have to use binary to compare this photo_id (which is being handed to me from a different outside service). So that I avoid 'uknn' being pulled out of the datbase instead of 'UKNn'. The problem is that when I do the explain, i see the use of binary doesn't use the index. If I take out binary it uses the index for photo_id. Is there a way to be able to use the binary option and use an index with it?

Original source