1

I receive around 30k price updates for various articles every minute and I need to write the new prices into our database (Which is a MySQL Database running InnoDB)

The table containing the prices right now has only three columns: Article_id, InsertTime, and ArticlePrice

My Problem is: I do not want to insert duplicate prices, but I want to store historical data. So when the price goes up or down I insert it. When it stays the same I skip it. Since I receive a lot of data I can not query first and then decide if I can insert or not, as this approach will be too time consuming.

Can this be solved on a database level? For example by adding a time column and using it as a primary key?

I also thought of solving it in the application itself, for example by keeping a List of the previous prices and compare them there before inserting into the database, but I want to guarantee a consistent database and this seems complicated.

Any help is appreciated.

  • Of the 30k prices, how many change every minute? Rough average? – Michael Vincent Jul 29 '16 at 7:54
  • Every price changes roughly 2 times a day. So I would be inserting a total of 60k rows each day. – Martin Jul 29 '16 at 7:57
  • 2
    That's about 42 a minute, isn't it? My approach, as more of a application developer than an SQL guy, would be to put it in the application, which you have suggested. It doesn't seem too arduous. – Michael Vincent Jul 29 '16 at 8:10
  • Yeah if I don't come across a brilliant solution to solve it in the db itself I will do just that. Thanks! – Martin Jul 29 '16 at 8:18
  • I'm going to keep an eye on this to see what people suggest. I did think about using a merge / upsert, but it looks like MySQL only has ON DUPLICATE KEY. This may still be possible to use if, as you suggest, you add a last inserted time column as part of a unique key. Maybe worth looking at if you haven't already. – Michael Vincent Jul 29 '16 at 8:40
1

You have 30K new records for the 'current' minute; put them in a temp table; include PRIMARY KEY(ArticleId).

Note: History will need PRIMARY KEY(ArticleId, InsertTime).

You need to find the 'latest' existing price for each of the 30K articles in the existing history table. But first, you need to find the latest InsertPrice for each article in this table: (This is 'select#1')

SELECT ArticleId, MAX(InsertTime) AS InsertTime
    FROM History
    GROUP BY ArticleId

Now back to the latest price (select#2):

SELECT ArticleId, ArticlePrice
    FROM ( select#1 ) t1
    JOIN History  USING(ArticleId, InsertTime)

Now let's find the changed prices (select#3):

SELECT temp.ArticleId, temp.LastInsert, temp.ArticlePrice
    FROM ( select#2 ) t2
    LEFT JOIN temp  USING(ArticleId)
    WHERE temp.ArticlePrice != t2.ArticlePrice

I may have also allowed for 'new' Articles to arrive. (But I am not sure.)

Now, let's copy the new Prices over:

INSERT INTO History (ArticleId, InsertTime, ArticlePrice)
    select#3;
  • I think this solution may work. I will test it with a small prototype. Another idea: I could have a table with all the most recent prices with a composite primary key on ArticleId and ArticlePrice and then do a INSERT with ON DUPLICATE KEY and if a price or article is new, I could update the timestamp and price. Afterwards I can select the entries with a new timestamp and carry these over to the original table. – Martin Aug 1 '16 at 7:52
  • You probably need such a non-history table for other purposes? – Rick James Aug 1 '16 at 15:05

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.