CODEWITHSUNDEEP
mysqldatabaserelational-database
SiberianGuy
SiberianGuy

Reputation: 25302

Compare strings length equality

I would like to know which of two following operations under indexed varchar field is more efficient (they play as alternatives in my more difficult query):

length(field) == 5

or

field == "12345"

First it looked obvious for me that first clause if quicker. But as field is indexed comparison is rather quick. But what about length? I guess index doesn't store information about string length...

Upvotes: 1

Views: 462

Answers (2)

RReverser
RReverser

Reputation: 2036

In any case, length comparison is better. Even if that strings are hashed and indexed in any way, their comparison can't become faster than simple integer comparison. Even if length is not stored (but I don't believe that), empty loop while(s[i] != '\0'); for length calculation will be faster then char-by-char loop somparison.

Upvotes: 0

Paul Sonier
Paul Sonier

Reputation: 39480

The second is far more efficient in an indexed varchar field. Specifically, the restriction criteria (exact match) is far more restrictive than a length restriction; that greater restriction speeds up the selection.

Upvotes: 2

Related Questions