MySQL Performance: # of columns vs varchars

| | Comments (0) | TrackBacks (0)

I have long told fellow developers that trying to stick to tables that have entirely fixed width columns is good practice.

This tends to lead to, at the least, normalizing out text on a performance sensitive table/application.

Instead of:

CREATE TABLE foo ( idx int(10) unsigned not null auto_increment primary key, productId int(10) unsigned not null, price decimal(7,2) not null, description text not null default '', KEY productId_idx(productId) );

It might be broken up into:

CREATE TABLE foo ( myInt int(10) unsigned not null auto_increment primary key, productId int(10) unsigned not null, price decimal(7,2) not null, KEY productId_idx(productId) ); CREATE TABLE foo_text ( foo int(10) unsigned not null primary key, text not null default '' );

The idea is that if you're doing most operations with the "foo" table, you'll get much better performance without a variable width column (varchar, text, etc).

They used a table with a varchar(1) column, and got a massive performance hit, but only when the number of columns was large. I'd like to experiment to see if it is the number or pure width of the columns. (In short, compare something like 99xchar(3)+1 varchar(1), or 1xchar(297) + 1 varchar(1).

0 TrackBacks

Listed below are links to blogs that reference this entry: MySQL Performance: # of columns vs varchars.

TrackBack URL for this entry: http://www.mattwallace.net/mt-tb.cgi/182

Leave a comment

September 2010

Sun Mon Tue Wed Thu Fri Sat
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30    
Creative Commons License
This weblog is licensed under a Creative Commons License.

About The Author

Matt Wallace is a cloud computing architect, and recovering Web Application Developer working on a large e-commerce site and dabbling in social networking applications. He has recurring dreams of manipulating the real world with jQuery.