12

Why do so many developers set varchar to 254 and not 255 when creating MySQL tables?

Proof that this happens: mysql varchar 254

BaUn
  • 159
  • 3
  • 9
  • 1
    Maybe some are scared going really to the limit! Living on the edge! :) – juergen d Apr 06 '12 at 12:43
  • @Jason_vorhees: I have also seen varchar(254) and wondered what the decision making process was for that. I write it off as "there was none; it's arbitrary dumbness / negligence", but I am open to being wrong. Interested to hear an answer to this. – tenfour Apr 06 '12 at 12:47
  • I am not form those developers –  Apr 06 '12 at 12:47
  • 2
    149000 results vs 1190000 for varchar(255) :) – Maxim Krizhanovsky Apr 06 '12 at 12:48
  • @tenfour may be its a chance that i never seen maximum 254 limit in any of table but i didnot think that there could be ever any valid reason for it at all. Its just a wild selection of some person without thinking. – Ankit Sharma Apr 06 '12 at 12:51
  • There's a post out there somewhere that states that using a VARCHAR(255 - 1 byte) for a column gives a problem when the entire content is indexed, the index would be 256 (2 bytes) then. Storing the column as 254 characters OR just only indexing the first 254 characters would presumably have the index be 255 (one byte) long. But if someone could point me to some documentation about index size being column size + 1 character... – Konerak Apr 06 '12 at 13:00
  • I <3 Google. 2 minutes after this question was posted, it becomes the first result for the search query. Google <3 stackoverflow. – Konerak Apr 06 '12 at 13:18
  • @BaUn change your selected answer because there is one better one with exect explanation – Ankit Sharma Apr 06 '12 at 13:25

2 Answers2

14

Your Google query gave you the hints already. One of the first hits is this:

https://www.vbulletin.com/forum/project.php?issueid=32655

It basically says, that FULLTEXT indexes on VARCHAR(255) require twice the space of a VARCHAR(254) FULLTEXT index. And some more other bloat on top of that.

I think this is far more important than saving one byte in the data table.

A.H.
  • 63,967
  • 15
  • 92
  • 126
  • gud answer, finally i got to know its importance, thanx for sharing it. – Ankit Sharma Apr 06 '12 at 13:11
  • Do remember this is NOT enough reason to use 254 varchar instead of 255 varchar. You can just create the index to hold 254 characters instead of 255 - this way, your column holds all the data you need, and your index does not get bloated. Also, this "bug" should really be reported to the MySQL server team so they can investigate and either fix or explain the confusion. The 'phpMyAdmin' screenshots in that vbulletin post don't give me much confidence... – Konerak Apr 06 '12 at 13:17
  • @Konerak Please feel free to do some experiments on your own and report the results. However: Even if this "behaviour" (be it a bug or not) would have been fixed _now_ the habit of simply using the limit 254 will stick around for a _very long_ time. People are this way. – A.H. Apr 06 '12 at 13:26
  • I agree, and if it helps, they should. But instead of limiting the column length, limit the index length, that appears to be where the problem is. – Konerak Apr 06 '12 at 13:29
  • 3
    The link is now dead. – broadmonkey Sep 18 '17 at 14:34
6

varchar fields require n+1 bytes for fields less than or equal to 255 and required n+2 bytes for fields > 255

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

It should be set to 255, I'm assuming developers think they will save an extra byte from 254, but 255 is the standard

Keith
  • 310
  • 4
  • 10
  • 1
    I agree, I'm saying 255 is right, not 254. It doesn't really matter too much nowdays. When db's first came out and hard drive space was very limited, it would be a big issue. – Keith Apr 06 '12 at 12:58
  • Half-true: this depends on the storage engine. True for MyIsam, not so much for InnoDB, don't know by heart for the other engines. – Konerak Apr 06 '12 at 12:58