Mysql : SELECT with WHERE BINARY is very slow

Question

For case-sensitive comparison, I am using WHERE BINARY in my query. But it's extremely slow. The column which I am using in WHERE BINARY is also indexed. How can I speed up the execution time of my SQL.

Here are my queries:

SELECT * FROM `videodb` WHERE BINARY pageurl= 'Hum-Hai-Bade-Miyan-Chote-Miyan-Bollywood-Action-Movie-Mamootty-Hansawardhan-Leena-Sidhu';

Showing rows 0 - 0 (1 total, Query took 0.1522 seconds.)

SELECT * FROM `videodb` WHERE pageurl= 'Hum-Hai-Bade-Miyan-Chote-Miyan-Bollywood-Action-Movie-Mamootty-Hansawardhan-Leena-Sidhu';

Showing rows 0 - 0 (1 total, Query took 0.0015 seconds.)

Answer 1

When you use BINARY with WHERE clause, MySQL does not use any indexes on the column. That's why your query with WHERE BINARY is extremely slow. The use of BINARY on the non-binary column is not recommended.

If the type of your column is CHAR or VARCHAR, change it to BINARY or VARBINARY. Create index on that column and run your query without BINARY.

I.e. use the following SQL after changing the type of the column to BINARY or VARBINARY. Your query will execute faster.

SELECT * FROM `videodb` WHERE pageurl= 'Hum-Hai-Bade-Miyan-Chote-Miyan-Bollywood-Action-Movie-Mamootty-Hansawardhan-Leena-Sidhu';