0

I've built an application that fetches reports from an external source. It downloads report data once a day. Each report covers the time from now to 30 days ago.

E.g. let's say it's 1 May and report is downloaded for the last 30 days and we end up with 30 rows in the table (let's have each day be represented by one row). Then on 2 May we fetch the report and we end up with 31 rows in the table - 1 row from the report fetched on 1 May, and 30 rows from 2 May. 29 older rows that overlap are discarded. The rows from the latest report replace them. Then on 3 May we end up with 32 rows in the table, 4 May, 33 rows, etc.

Now the query that does the clean-up of the older data in my application looks like this (records with the same date that have a lower ID are deleted):

DELETE FROM $tableName WHERE id NOT IN (SELECT MAX(id) FROM (SELECT * FROM $tableName) AS sth GROUP BY $date

The problem is that this query is very inefficient. As the amount of data in the table grows, it's starting to kill the database server as each the select statement is applied to the whole dataset. Aslo NOT IN and MAX become less and less efficient as number of rows grows.

The system has to update rows from the past as the external source normalizes data backwords in time so records from the past will change occasionally, and I'm always interested in the last 30 days.

I'd be grateful for advice on how I can optimize this part of the application, how I can overcome the performance problem of this SELECT and DELETE statement.

1

Define an unique key in your table such that each day can only be represented by one row (UNIQUE KEY(date) would be enough for the example you show). Then use

INSERT INTO $tablename(...) VALUES(...)
    ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2);

Alternatively you might use REPLACE INTO, but that does the delete and insert, "burning" through autoincrement IDs which you seem to have.

You might even use the date column as a PRIMARY KEY with this setup.

  • Thanks you for that advice. However, I've simplified my question a bit as the data cannot in fact be a UNIQUE KEY as there are reports that have the same date in several rows. My 'clean-up' query account for that like this: DELETE FROM $tableName WHERE id NOT IN (SELECT MAX(id) FROM (SELECT * FROM $tableName) AS sth GROUP BY $duplicateDefinition, where $dulicateDefinition can be multiple columns, e.g.: date, campaign_id. Can your solution be adjusted to account for that? – luqo33 Jun 4 '16 at 11:47
  • @luqo33 yes, the unique key (and even the primary key) can contain multiple columns too, so if there exists a set of columns which identifies the daily row (the list in your GROUP BY), just use that to define the key. – jkavalik Jun 4 '16 at 11:53

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.