0

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.

Improve this question
3
  • 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, 2019 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, 2019 at 21:55
  • 1
    What would be wrong with a VIEW?
    – Michael Kutz
    Nov 19, 2019 at 0:38

1 Answer 1

Reset to default
2

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".

Improve this answer
7
  • Do you know anything about what performance hit, if any, I should expect from this?
    – Magnus
    Nov 19, 2019 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, 2019 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, 2019 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, 2019 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, 2019 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.