We are planning to store a large number of product
descriptions in a MySQL database. There will be at least
a dozen fields:
productID int(12) unsigned DEFAULT '0' NOT NULL auto_increment,
name char(50) DEFAULT '' NOT NULL,
division char(50) DEFAULT '' NOT NULL,
attributes tinyint(2) DEFAULT 0 NOT NULL,
...
PRIMARY KEY (productID)
We also need to store a long description field, in a
small BLOB or TEXT field. The size of this field will be
about 1k, so the field type will probably be TEXT (as opposed
to TINYTEXT, MEDIUMTEXT, etc.).
This table will have many records inserted and deleted
every day. The size of the database: 500k - 1 million records.
We estimate that we'll be adding and deleting tens of thousands
of records a day.
So the question is: should we split this into two tables --
one for all the int and fixed-length character fields, and another
for the TEXT field ? Would this alleviate any problems, or
would it just make queries take longer (because we'd have
to retrieve the TEXT in a separate query, or perform a join) ?
I've seen busy, large tables (with BLOBs) get fragmented and
even corrupted, and I'm wary of getting into a situation where
we have to take the database offline every week to run
isamchk -r over it. My thinking is that if we isolate the TEXT
fields, we isolate any risk of corruption. Also that simpler
table would probably rebuild more quickly in the event of
corruption, because the only two fields would be
productID int(12) unsigned DEFAULT '0' NOT NULL,
longdescription TEXT,
PRIMARY KEY (productID)
...right ? Or is this just a bad idea ?
--
matt.
