0

Given three tables:

(a) Product (b) Transaction Head (c) Transaction Detail

We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction date.

We also have a web page where the transaction can be created, edited and deleted.

The problem lies in the query performance. The data in the transaction head and detail tables may increase dramatically fast. Hence, executing a query with a subquery which finds the most recent transaction can be heavy.

We came up with a solution to create a summary table where it may contain the following columns: (a) branch id (b) product id (c) latest quantity ordered (d) latest ordered date

which will be updated in the transaction module. However, when a transaction entry is deleted, we had to run the whole query, order by date descending, and update the summary table since the previous state is not stored.

Is it advisable to asynchronously update the summary table using the heavy query every time a transaction is deleted? Would not it be too heavy for the server? We are also considering using cron - a job scheduler to synchronously re-compute the summary table for efficiency, but we had to display the information at real-time.

Improve this question
3
  • How big numbers do you mean with "The data in the transaction head and detail tables may increase dramatically fast." ? like million rows in a year? or in a month? Mysql can handle quite big tables and queries may be fast enough when properly indexed.
    – jkavalik
    Commented Aug 20, 2015 at 11:45
  • Yes. It's possible to reach 1 million in a year.
    – Xegara
    Commented Aug 20, 2015 at 12:20
  • You can generate testing data (random is often enough) of 1 million and check queries you want to use? To see if you can get realtime results fast and what impact does the "heavy" query to update summaries have. That way you can get better estimates of what needs to be optimized. You can probably ask about optimization of specific queries if they seems slow at first. I expect the summary query to be worse than realtime summary computation if it has to be done on every update of the table - but you could maybe use triggers instead to do partial updates to summary table on each modification.
    – jkavalik
    Commented Aug 20, 2015 at 12:56

1 Answer 1

Reset to default
0

1M/year is 2 per minute. You can do a lot of updating in 30 seconds. 1M/day (12/second) would be more exciting.

Do you need to rerun the summary queries? Can't you just do a subtract via an UPDATE? Possibly fast enough to be synchronous.

How often do you need to post a correction? If it is rare, consider adding a row to the summary table with a negative number. Then, when you do your reports, the SUM() comes out right. (You do sums, correct?)

It seems just wrong to have summary tables but to continually rebuild the table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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