Mysql – How to keep history of a thesql table that has autonum as key

MySQL

My solution to this (probably typical) Mysql problem is horror performancewise, see below. But first the problem.

I've got a table

prices (fkey AUTONUM*, b STRING[3], c STRING[3], validon DATETIME, price FLOAT)

fkey is the int32 primary key. The (b,c) pair is another UNIQUE index and is needed for various reasons.

The following kind of queries are executed all the time and are the only way to update or insert into the table:

INSERT INTO prices (b, c, validon, price) VALUES (...),(...),...
ON DUPLICATE KEY UPDATE price = VALUES(price)

So autonum runs freely as needed and price is updated if a clash is encountered.

This works superb, until I need to have another table "history" which keeps track of all prices ever inserted or updated. I designed it like this:

history (a INT32, validon DATETIME, price FLOAT)

This table has only one key – the combination of all three columns.

My question is: How to insert rows in table "history" as rows of table "prices" are being inserted/updated? The issue I have is to retrieve the correct autonum values quickly and efficiently.

Right now my solution in pseudocode is this:

LOCK TABLES prices, history WRITE;

k := SELECT MAX(fkey) + 1 FROM prices

// add_to_history() always returns 0 and has only one statement :
// INSERT IGNORE INTO history VALUES (a, b, c);

INSERT INTO prices VALUES ......
ON DUPLICATE KEY UPDATE 
    price = if(validon < values(validon), 
         values(price) + add_to_history(fkey, values(validon), values(price)), 
         price),
    validon = if(validon < values(validon), values(validon), validon)

INSERT IGNORE INTO history 
SELECT fkey, validon, price FROM prices 
WHERE fkey >= k;

UNLOCK TABLES;

The solution "works", but it blocks the entire server occasionally for random periods of time (not indefinitely, but for hours) and it must go.

Both tables can be either MyIsam or InnoDB (right now they are MyIsam). Please let me know if a certain DB engine is needed to solve this properly.

Best Answer

In my opinion your best bet is adding a trigger to the prices table after the insert so that it adds the desired information in the history table. You will need another trigger that is called after update to deal with insert collitions.

After that you go on simply inserting in prices as you have been doing all along and let MySQL do the rest.

Example for insert trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER INSERT ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price)
    /* Example insert end */
END $$
DELIMITER ;

Example for update trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER UPDATE ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price - OLD.price)
    /* Example insert end */
END $$
DELIMITER ;

Please change the triggers' insert queries since I do not understand your history table and these examples will fail on duplicate ids.

Related Question