Hi,
I'm hoping someone on this list can save me some unnecessary
benchmarking today
I have the following table in my system
BIGSERIAL , INT , INT, VARCHAR(32)
There are currently 1M records , it will grow to be much much
bigger. It's used as a search/dispatch table, and gets the most
traffic on my entire app.
I'm working on some new functionality, which will require the same 3
colums as above but with 3 new VARCHAR(32) columns
BIGSERIAL , INT , INT, +VARCHAR(32) , +VARCHAR(32) , +VARCHAR(32)
ie, the new function shares the same serial and the the 2 INT columns
I'm trying to get this to work efficiently on speed and on disk space.
i've figured that my options are:
a) one table with everything in it
pro:
simple
possible con:
when i had something similar in mysql 4 years ago, i had to make
all the varchars chars , because speed was awful. under this system,
80% of the 3 new VARCHAR fields will always be null, so that disk
waste will be noticable. thats only IF there is a speed issue with
VARCHAR searching.
b) keep current table, create new table that inherits and has the 3
new fields
pro: simple
possible con:
i can't find any documentation on how an inherit works behind the
scenes. is the data cloned into the new table? is there a join on
every search? if this is constantly doing a join behind the scenes,
thats probably not going to work for me
c) move to a 3 table structure
table1- serial
table2 - current table, bigserial is not bigint
table3- bigint + 3 varchars
pro:
obviously will work
con:
a lot of restructuring
i was going to have both table share a seqeunce, but then i
remembered that the id is foreign keyed by other tables
if anyone can offer a suggestion, i'd be greatly appreciative