I have a table big_table with a large number of daily INSERTs, and this table has a column interesting_val. For the first row, and first row only, which is inserted after 00:00 UTC on each date, I want to SELECT interesting_val and INSERT to another table daily_interesting_val. Is this behavior something I can achieve with a combination of procedures and triggers?

Whenever big_table has at least one row created after 00:00 UTC, but daily_interesting_val does not have a corresponding row with the new interesting_val, my application is in an illegal state, and I want to make this time window as small as possible.

One solution I can imagine, but don't if is possible in MySQL:

Create a scheduled procedure that creates a trigger at 00:00 each day. This trigger calls a procedure which (1) checks existence of row, (2) if not exists, insert row, (3) if exists, delete trigger.

  • I edited my question a bit. The scheduled procedure is undesirable because I have no way of exactly knowing when big_table gets the first INSERT for the day, and I can't tolerate more than a few seconds where my application is in the illegal state. – Magnus Nov 18 '19 at 21:50
  • 1
    To me, the ultimate fix is re-writing the logic of the application. If that logic cannot be completely re-written, a trigger would resolve the issue but you will have performance hits. Is there a duration you'd expect the value to occur? Could you schedule a job to do the check every 30 seconds for the first half hour of the day? That would possibly be a lighter hit than the trigger depending on how many transactions occur on this table. – Shaulinator Nov 18 '19 at 21:55
  • 1
    What would be wrong with a VIEW? – Michael Kutz Nov 19 '19 at 0:38

Use INSERT IGNORE and have a unique key on the date. The first insert to run for the day will succeed; the rest will be "ignored".

  • Do you know anything about what performance hit, if any, I should expect from this? – Magnus Nov 19 '19 at 13:49
  • 1
    @Magnus - "not much". How often is trigger firing? How many queries per second are you executing now? 100 qps is easy; much more is possible. Think of this as adding one more query. – Rick James Nov 19 '19 at 14:12
  • Thanks for elaborating. Is this a type of overhead you would deem acceptable if this was a DBMS you were responsible for? You say it's possible with a lot more than 100 qps, but does that mean it's a good idea, or at the very least, not a bad idea? – Magnus Nov 20 '19 at 8:32
  • 1
    @Magnus - INSERT IGNORE will be faster than SELECT + INSERT. It is very reasonable to use it where appropriate. You seem to be in "premature optimization mode". Get farther into development. Then show us a biggeer picture of where performance is actually a problem. – Rick James Nov 20 '19 at 18:48
  • 1
    A million inserts a day, even with a trigger doing the INSERT IGNORE, will be easily performed. A billion a day is a different matter; and the solution won't be found in this lowly INSERT IGNORE. – Rick James Nov 20 '19 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.