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, c STRING, 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.