CODEWITHSUNDEEP
mysql
Divyanshu Jimmy
Divyanshu Jimmy

Reputation: 2752

Impact on MySQL SELECT query if we search a string with length greater than column's length

I have a column field "name" with type VARCHAR and length as 80 . If i search for a string with length greater than 80 , what is the behaviour of SELECT operation in this case ?

Apart from round trip to DB , will it scan whole table or just perform a return as the length is already greater than 80 ? 

SELECT * FROM TABLE WHERE name = "longgg... ...string" ;

I need this knowledge because in the existing codebase there is a variable which is used in all layers of MVC . This variable is meant for a different column with different length . For saving time and code redundancy I just want to use same variable which has validation for a bigger length as compared to 80.

After the discussion , I am going to add length validation instead of depending on db validation as all the rows are scanned !

One more wise thought in the comments , if the column is indexed whole table is not scanned .Verified same using EXPLAIN :

All rows are scanned as there is no index. CHAR/VARCHAR index stores length as well (included in explain/key_len). So if index created on the column, the query should not compare anything as based by the index SQL should understand that there is no rows found by criteria.

Upvotes: 1

Views: 303

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I don't have a documentation reference for this, but from my observations testing what will happen is that MySQL will cast both sides of the equality expression to the length of the string literal on the RHS. That is, MySQL will execute the following query:

SELECT *
FROM yourTable
WHERE CAST(name AS CHAR(100)) = CAST('longgg... ...string' AS CHAR(100));
-- assuming it has a length of 100

This comparison would generally fail, since MySQL will not pad a string on the LHS which is less than 100 characters, meaning that the lengths would not generally even match.

Upvotes: 1

Related Questions