0

I do have a database with a table with more than 300mi entries. There is near of 300 inserts per minute,

Very often We need to get statistical data. (count, sum avg). Each query takes from 10 to 30 seconds to be completed.

From the start of the query to its end, the table data increases more than 100 entries.

The numbers I get from the statistic query are related to the start of the query or to its end?

I'm using mysql and innodb

Daniel Santos
  • 14,328
  • 21
  • 91
  • 174
  • 1
    Possible duplicate of [transaction isolation level good explanation](https://stackoverflow.com/questions/3532579/transaction-isolation-level-good-explanation) – Sam M Sep 19 '18 at 01:13
  • If you are not happy with the 10-30s queries, consider building and maintaining Summary Tables. 5 inserts/sec is 'slow'; 500/sec is 'fast'. – Rick James Oct 06 '18 at 19:40

1 Answers1

1

Regardless of your transaction isolation level (e.g. repeatable-read or read-committed), any single SQL query against InnoDB tables gets a sort of "snapshot" or a consistent view of the database.

A SELECT query will not block concurrent inserts or updates, but those inserts or updates won't influence the result of the SELECT query. It will "see" the data as it existed at the start of the SQL query.

If you use repeatable-read transaction isolation level, all queries in the transaction, including potentially several SELECT queries (at least non-locking queries), sees a consistent snapshot of the data, even if concurrent inserts are continuing.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828