Hello, MySQL Experts,
I wonder if you could give me some advice on a database design problem.
I have a table which will have many more reads than writes (between 10 and
100 times). I wish to make
updates to that table and to a slave table transaction safe. The basic
structure is of a master slave
relationship with a one-to-many relationship between master and slave. I
assume that I create a
row in the master table, getting a unique ID from autoincrement, then use
that ID to key all the entries
n the slave tabel. The problem is that the master record must not be
visible until all the slaves are
visible. I do not mind (much) if I get orphan rows in the slave table. But
I do mind if I get visible entries
in the master table but some of the rows never made it to the slave.
I see two approaches. I can use transaction safe table types (BDB or
Innobase). Or I can create the
row in the main table with an "incomplete" flag, then clear the flag when
the slave table has been safely
updated. General MySQL documentation suggests that one can usually engineer
transaction-safety
into the application, and that this is to be preferred if you want high
performance.
I am worried about speed, which is important to me. As I said, reads
overwhelmingly dominate, and
(indexed) speed is a major concern. Are transaction-safe tables slower than
MyISAM tables for
read-only access, and if so how much? Do both tables need to be
transaction-safe, or only the
master?
Alternatively, is there any way I can safely generate a unique id without
creating the master row?
The I could put the slave rows in before I create the master row, and I
wouldn't have a problem.
Thanks in advance for any advice.
Alec Cawley
<<--
This e-mail is intended for the named addressees only. Its contents may be
privileged or confidential and should be treated as such. If you are not
an intended recipient please notify the sender immediately; do not copy,
distribute, or take any action based on this e-mail; and then delete it.
In the pursuit of its legitimate business activities and its conformance
with relevant legislation, Quantel Ltd. may access any e-mail (including
attachments) it originates and receives, for potential scrutiny.
To learn more about Quantel's Clipbox Studio, go to
http://www.quantel.com/clipboxstudio
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
