[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!)
MySQL 5.0.3 and later support varchar fields up to 65,535 characters.
Nope. The maximum row size in InnoDB is 65,535. The maximum size of a varchar, provided it’s the only column in the table, is 65,532. But again, it has to be the only column in the table.
If you tell MySQL to make an InnoDB table with a 65536 character varchar it silently converts it to a TEXT column for you. AWESOME!
As cited in the Rubyism article you referenced:
“This leaves us with the problem of avoiding the star in the ‘select *’ that AR generates. For this as well, there is an option to find(): the find method accepts an option hash as its final parameter, and that option has not only allows for the usual :conditions, :order or :limit options, but also for ’:select => [ fieldlist ]’.”
So, its not so bad that the column was text (better to go over limit, then to go under limit … and have to try to fix malformed urls later with regex/hand-tuning the db data). To find the culprit(s) look where it was used in queries/joins. Select *’s are bad for nearly any database …. pulling out too much data in bad for overall performance. So always limit selected criteria via :select.
Scott: you’re right. Thanks, I’ve updated the post to reflect this. I feel stupid for not looking it up before writing it. Considering the program we were working on ended up with text columns, it seems like I’m not the only one who hadn’t heard this.
This is good information to know, about the VARCHAR. I too have been using TEXT to get around the limitations of such short string lengths. Thanks for this post. Looks like I have some migrations to get to writing. ;)
Can we talk about how RoR considers a MySQL TINYINT to be boolean when it is supposed to be 0-9?
Tony—
Sorry your comment got delayed in posting. Our over-eager spam filtering caught it.
You’re sort of right. The maximum row size is ~65K. So if one of your varchar columns uses up all that space, you can’t have other columns.
But you can distribute this however you like (four 16K varchars, or a ton of 1K varchars). This is pretty common in other databases. You’ve got a certain amount of space to work with for each row. (I wonder how it works with Postgres with their 1GB varchars…)