CODEWITHSUNDEEP
mysql
Lawrence Cooke
Lawrence Cooke

Reputation: 1597

Force binary in query

I have a field in my database which the majority of the time, the case sensitivity doesnt matter.

However there are specific circumstances where this isn't the case.

So the options as I see it are, add the BINARY keyword to the query

select id from table where BINARY fieldname= 'tesT'

or alter the table to make it a case sensitive field.

The question is, does using the BINARY keyword cause additional overhead on the query, or is this negligible.

(it would actually be used in an update where statement)

update table set field='tesT' where BINARY anotherfield='4Rtrtactually '

Upvotes: 1

Views: 170

Answers (1)

hubson bropa
hubson bropa

Reputation: 2770

The short answer is binary has been recommended in a similar SO question, however whether overhead is negligible depends on other parts.

Part one is how you define negligible. If you mean by performance then define your negligible threshold return time(s) and try selects using binary and not using binary to see what kind of return time you get (these need to be cases where case won't matter). Further this by doing an explain to root out any difference.

The second part is it depends on how big the table is and how the where clause column is indexed. Generally speaking, the more rows there are then the more prominent any difference would be. So if you're looking at say ten records in the table then it's probably nothing given your doing a single table update. Ten million records then better chance to be noticeable.

Third part is based on Yann Neuhaus' comment in the MSQL 5.7 reference guide. It might depend on exactly where you put the binary keyword. Assuming his findings are accurate then putting the binary on the constant side of the equals might be faster (or at least different). So test a rewrite of your update to:

update table set field='tesT' where anotherfield = BINARY '4Rtrtactually '

To be honest I have my doubts on this third part but I've seen weirder MySQL magic. Kinda of curious if you test it out what the results would be for your scenario.

Final part might be what type of collation you are using. Based on your question it seems you are using case insensitive collation. Depending on how you are using this data you might want to look into case sensitive or binary collation. This SO question was educational for me on this subject.

Upvotes: 1

Related Questions