Absurd difference in WHERE without BINARY conversion

Asked

Viewed 47 times

0

I implemented an INDEX in the "Name" column, which is varchar Binary (latin1_binary).

This query takes about 1.5 seconds to execute:

SELECT Nome FROM contas WHERE IP='255.255.255.255' AND Nome != BINARY'uhdde23221' LIMIT 10;

However, this takes 43 seconds:

SELECT Nome FROM contas WHERE IP='255.255.255.255' AND Nome != 'uhdde23221' LIMIT 10;

This, even without conversion Inary, takes 0.001s:

SELECT * FROM contas WHERE Nome='uhdde23221';

Why does this happen? I will always have to use "BINARY"?

Version: 10.4.14-Mariadb

mysql

  • I believe that there is a difference between the two, but only by testing, the difference is that the BINARY differentiates capitalized and lowercase (while the without, I believe that does not differentiate). Also, if this is a frequent query, try creating an index (IP, Name), I don’t know if it will help much. It seems to me that IP is more important than Name, the name is only in !=. And... if the IP, Nome are unique (there is no data with same IP and Name), you can simply do WHERE IP = and then in its application ignores whether the Nome is the same, sometimes it’s faster than using IP = along with Nome != to take out 1 line.

  • In its edition, as Nome =, you are using the =, while in others equality is based on IP = (and the Nome are using as Nome !=).

  • I believe the right way to analyze it is by using the explain and analyze and seeing the query execution plan.

  • The time of 1.5s is normal since I didn’t actually do an index on IP. The problem is the 43 seconds when I match the column Nome with any other in the clause WHERE without converting the static part into BINARY. This did not happen before, only after I did an index in Nome. What should speed up the query ended up making it absurdly slow for this detail, and I don’t know why. OBS: The column Nome is already binary by default.

No answers

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.