Mysql – Varchar storage and performance

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.

As the docs say the min size occupied by a varchar is 1B and the max is 256B (before 5.0.3) and 65,536B (after 5.0.3)

But when it comes to look-up performance always searching on a char will be faster then a varchar (it makes sense since you will have fixed size rows then the look-up process will be much faster because it can calculate exactly where in the data the desired value is )

EDIT

If you have varchar(100) and inserted a string of 10 chars then the space occupied is 11B (varchar uses 1B to store the length of the string max. 255 for ver <= 5.0.3 and 2B for ver > 5.0.3 since max. 65,535).
If you have char(100) and inserted a string of 10 chars then the space occupied is 100B.