I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction.

As per the calculations, several thousand transactions per day and this table would hit few million entires withing next few months.

1) When querying and writing data, would server perform very slow?

2) Is there any best way to handle the situation?

  • Simulate that situation with random data. A finance institution surely can permit itself a couple of benchmarks? If not a DBA. – Gerard H. Pille Mar 6 '18 at 9:26

The obvious answer is: COUNT(*). There is no optimisation for this, it just counts all entries in the primary key.

Of course anything requiring a full table scan or a full index scan will be really slow.

Optimised queries should not be slow. Slower? Yes, because indexes will be quite big. You can always partition the table, and your queries will read smaller portions of indexes (but don't expect any type of parallelisation).

Of course to be sure about the performance you should run a test. If you have the applications queries written in a binary log or slow log, replay the log with some tool or a custom script. If you don't have, write some realistic queries and make a test with sysbench (some trivial lines of lua language are required).

| improve this answer | |
  • count(*) might be an obvious answer, but not for the question at hand ,) – eckes Mar 6 '18 at 20:09
  • I think it is. With a small table it's fast, with a big table it's slow. – Federico Razzoli Mar 6 '18 at 22:11
  • Actually, InnoDB uses the smallest index, which is usually not the PK. Still, it is slow. – Rick James Mar 9 '18 at 2:39
  • But who does COUNT(*) on the whole table? That is not a very useful query, so don't knock the product or the 50M-row table because it is slow. – Rick James Mar 9 '18 at 2:40
  • I've seen it many many times, unfortunately. – Federico Razzoli Mar 9 '18 at 10:24

I have seen a billion-row table hum along fine.

I have seen a thousand-row table cause the bones of the server to creak and groan.

It all depends on the queries.

"several thousand transactions per day" -- No Problem. "several thousand transactions per second" -- This gets somewhat challenging, but probably doable.

What to do?

  • Provide the main queries.
  • Provide the main transactions.
  • Don't do COUNT(*)
  • Don't do anything that requires hitting all 50M rows -- ask for help in reformulating such queries.

If this is really a financial institution, be sure to hire a security consultant. You do not want the hassle you will get if the data is lost/stolen.

| improve this answer | |

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.