October 2009 Archives

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).

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.