List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 24 1999 2:26am
Subject:store BLOBs separately ?
View as plain text  

>>>>> "matthew" == matthew mcglynn <mcglynn@stripped> writes:

matthew> We are planning to store a large number of product
matthew> descriptions in a MySQL database. There will be at least
matthew> a dozen fields:

matthew>   productID int(12) unsigned DEFAULT '0' NOT NULL auto_increment,
matthew>   name char(50) DEFAULT '' NOT NULL,
matthew>   division char(50) DEFAULT '' NOT NULL,
matthew>   attributes tinyint(2) DEFAULT 0 NOT NULL,
matthew> ...
matthew>   PRIMARY KEY (productID)

matthew> We also need to store a long description field, in a
matthew> small BLOB or TEXT field. The size of this field will be 
matthew> about 1k, so the field type will probably be TEXT (as opposed
matthew> to TINYTEXT, MEDIUMTEXT, etc.).

matthew> This table will have many records inserted and deleted
matthew> every day. The size of the database: 500k - 1 million records.
matthew> We estimate that we'll be adding and deleting tens of thousands
matthew> of records a day.

matthew> So the question is: should we split this into two tables --
matthew> one for all the int and fixed-length character fields, and another
matthew> for the TEXT field ? Would this alleviate any problems, or
matthew> would it just make queries take longer (because we'd have
matthew> to retrieve the TEXT in a separate query, or perform a join) ?

It depends on how you use the tables;  If you are sometimes accessing
the default table without retrieving the blob information, it will
probably be faster to store the blob onto a separated table.

matthew> I've seen busy, large tables (with BLOBs) get fragmented and 
matthew> even corrupted, and I'm wary of getting into a situation where
matthew> we have to take the database offline every week to run
matthew> isamchk -r over it. My thinking is that if we isolate the TEXT
matthew> fields, we isolate any risk of corruption. Also that simpler
matthew> table would probably rebuild more quickly in the event of 
matthew> corruption, because the only two fields would be
matthew>   productID int(12) unsigned DEFAULT '0' NOT NULL,
matthew>   longdescription TEXT,
matthew>   PRIMARY KEY (productID)

matthew> ...right ? Or is this just a bad idea ?

Fragmentation is a problem with the current ISAM ; MyISAM in 3.23 will 
be MUCH better in this case!

On the other hand, the tables should NOT get corrupted (as long as the 
MySQL server hasn't died, which it very seldom does).

Yes;  Fixed size tables are by nature more secure, so it may not be a
bad idea to split your table up!


PS:  Sorry for the late reply;  Until 1999-06-01 (when we come back
     from USA), my replies may be a bit delayed.
store BLOBs separately ?matthew mcglynn21 May
  • store BLOBs separately ?Michael Widenius24 May