On Thu, Apr 26, 2001 at 04:31:40PM +0100, [EMAIL PROTECTED] wrote:
> 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.
Sure, if you're willing to create a one-row table to hold the sequence
number. You can use the 1-argument form of LAST_INSERT_ID() to create
values that will be treated like the most recent AUTO_INCREMENT value.
# create the table
CREATE TABLE seqnum (num INT UNSIGNED NOT NULL);
# initialize sequence number value
INSERT INTO seqnum SET num = 0;
# Generate a new sequence number
UPDATE seqnum SET num = LAST_INSERT_ID(num+1);
# see what it was
SELECT LAST_INSERT_ID();
# Generate a new sequence number
UPDATE seqnum SET num = LAST_INSERT_ID(num+1);
# see what it was
SELECT LAST_INSERT_ID();
# Generate a new sequence number
UPDATE seqnum SET num = LAST_INSERT_ID(num+1);
# see what it was
SELECT LAST_INSERT_ID();
# etc
---------------------------------------------------------------------
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
