List:General Discussion« Previous MessageNext Message »
From:matthew mcglynn Date:May 20 1999 11:18pm
Subject:store BLOBs separately ?
View as plain text  
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.


Thread
store BLOBs separately ?matthew mcglynn21 May
  • store BLOBs separately ?Michael Widenius24 May