[Update: I should really re-title this “(One reason why) MySQL used to suck” because as Scott Becker points out in the comments, as of MySQL 5.0.3, the maximum VARCHAR in MySQL is a respectable 65,535 bytes. So consider me appropriately chastened. I’ve updated the post below.]
I saw this recently in some code we were working on:
1 2 3 |
create_table :foobar do |t| t.text :url end |
Huh? Using a TEXT column to store a URL? That’s not good:
If any field with “TEXT” or “BLOB” in its type name becomes part of your SELECT query and the query is using temporary tables in its execution plan, the temporary table cannot be created in memory. Instead a temporary table on disk has to be used, regardless of MySQLs settings for tmp_table_size and max_heap_table_size.
Oh yeah, that’s right: MySQL (prior to version 5.0.3) limits VARCHAR fields to 255 bytes. Certainly long enough for most URLs, but somebody was just being careful.
Philip Greenspun pointed out a long time ago that one of the major indicators of database quality is how much data it lets you store in a VARCHAR. MySQL (prior to 5.0.3) is on the pathetically small side compared to other databases:
- PostgreSQL: approximately 1 GB
- MySQL (5.0.3+): 65,535 bytes
- DB2: 32,672 bytes
- Informix Universal Server: 32,000 bytes
- Microsoft SQL Server: 8000 bytes
- Oracle: 4000 bytes
- MySQL (prior to 5.0.3): 255 bytes
Supposedly this is going to be fixed in a future version. Someday. Sigh. (And it was! Hurray!)
