Mysql – Inserting 30k prices each minute, how can I skip an entry if the price did not change

database-designMySQLperformancequery-performance

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.

Best Answer

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;

Related Solutions

Mysql – optimize big sql query

All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

Use views and stored procedures instead of heavy-duty queries.
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to use UNION ALL statement instead of UNION

For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html

Thanks n regard

Internal Online Store Database Design Issue

You'll can solve the problem by adding more linking tables. You can alter your design to resemble something like this:

CREATE TABLE Items
(
    itemID INT PRIMARY KEY IDENTITY(1,1)
    ,itemDetails NVARCHAR(100)
)
GO
CREATE TABLE Regions
(
    regionID INT PRIMARY KEY IDENTITY(1,1)
    ,regionName NVARCHAR(100)
)
GO
CREATE TABLE AddOns
(
    addOnID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnDetails NVARCHAR(100)
)
GO
CREATE TABLE ItemAddOns
(
    itemAddOnID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT
    ,addOnID INT
    ,CONSTRAINT [FK_ItemAddons_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)
    ,CONSTRAINT [FK_ItemAddons_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)
GO
CREATE TABLE ItemAvailability
(
     itemAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT 
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_ItemAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_ItemAvailability_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)  
)
GO
CREATE TABLE AddOnAvailability
(
    addOnAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnID INT 
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_AddOnAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_AddOnAvailability_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)

Something like that setup will allow you to easily query the cost for any Items and AddOns by any region. If AddOns are not restricted to particular Items you can omit the ItemAddOns table.

Best Answer

Related Solutions

Mysql – optimize big sql query

Internal Online Store Database Design Issue

Related Question