Hi!
>>>>> "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!
Regards,
Monty
PS: Sorry for the late reply; Until 1999-06-01 (when we come back
from USA), my replies may be a bit delayed.
