Mysql – ny way to temporarily enforce columnar uniqueness on items currently being inserted

insertMySQLPHPuniqueidentifier

Is there any way to temporarily enforce columnar uniqueness on items currently being inserted? For instance, I'm splitting payments and charges to their own tables from a previously unified "transactions" table. Of course, the new tables have their own autoincremented ids and won't be cueing off the previously existing "transaction" ids so I was going to create a unique key for each transaction (based on an MD5 of the previous transaction_id and transaction_title) and use it as the new title. Basically the pseudocode of the PHP/SQL would go something like this:

$old_payments = getOldPayments();  //DON'T WORRY ABOUT THIS, IT WORKS

foreach($old_payments as $payment){
    $unique_md5_info = md5($payment['transaction_id']."_".$payment['transaction_title']);
    $query = sprintf("INSERT INTO payments (title, amount, due_date) VALUES('%s','%s','%s') WHERE title <> '%s'",$unique_md5_info,$payment['amount'],$payment['due_date'],$unique_md5_info);
}

Obviously, in a perfect world we'd only need to run this once and be done. However there are groups on multiple servers that won't be moving over at the same time so, in order to accommodate the staggered move, we'll have to run the query on different dates while being sure not to import any duplicate transactions.

Anyway, I'm probably seriously overthinking this issue, but hopefully someone has a reasonable solution.

Best!

Best Answer

You could use INSERT IGNORE. This would reject any INSERTs where the primary key or unique key already exists.

If you have to update non-keyed columns in a table in the event that the primary key or unique already exists, you can use INSERT ... ON DUPLICATE KEY UPDATE. I demonstrated how to use this in another question in the DBA StackExchange.

For your particular query, please make sure the title column is either the primary key or has a unique key defined. Then, INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE will work for you.

Give it a Try !!!

UPDATE

If the payments table is not too big, try this crazy ideas

Create a UNIQUE INDEX on this one-off

ALTER TABLE payments ADD UNIQUE INDEX title_amount_due_date (title, amount, due_date);

Perform your INSERTs IGNORE from the multiple servers. When done...

ALTER TABLE payments DROP INDEX title_amount_due_date

Related Solutions

Mysql – ny way to get recently inserted value in a table without using aggregate functions

Your existing index doesn't help you out at all, since you are actually asking for the last timestamp of every possible user. This means the only way for MySQL to answer this is to do a full table scan.

As @ypercube commented, an index on (user_id, login_time) is good for this kind of query - MySQL could utilize such an index without actually going to the table.

Adding an index to a table will indeed lock the table; but there are solutions to that as well. Take a look at oak-online-alter-table or pt-online-schema-change (disclaimer: I'm author of the first), both of which are scripts which allow online table alteration via simulation, triggers and incremental copy.

You might also want to solve this in a completely different way: create a new table like

CREATE TABLE last_user_login_time (
  user_id INT UNSIGNED PRIMARY KEY, 
  last_login_time DATETIME
);

And

INSERT INTO last_user_login_time 
  VALUES (the_user_id, NOW()) 
ON DUPLICATE KEY UPDATE last_login_time=NOW()

to this table upon any new login. Thus, keeping the last login time per user at any given time. This means more writes (an additional write per login), but I suspect this table should be very small and the impact proportionally small. Reading from it, of course, is trivial.

MySQL – How to Trace Optimizer’s Work

The recent 5.6 version has added this feature.

See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer

Typical Usage:

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";

SELECT ...; # your query here

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

# possibly more queries...

# When done with tracing, disable it:
SET optimizer_trace="enabled=off";

You can see it in action in SQL-Fiddle. Not sure how the output can be beautified.

Best Answer

Related Solutions

Mysql – ny way to get recently inserted value in a table without using aggregate functions

MySQL – How to Trace Optimizer’s Work

Related Question