CODEWITHSUNDEEP
mysql
Luciano Nascimento
Luciano Nascimento

Reputation: 2600

MySQL queries to generate table statistics

I have a MySQL database table that have around 10-15k inserts daily, and it certainly will increase next months.

- Table Example (reservations): *important fields*
+----+--------+----------+---------+-----+
| ID | people | modified | created | ... |
+----+--------+----------+---------+-----+

I need to provide daily statistics, informing how much entries had (total and specified with same number of people), based on a DATE or a date RANGE that user selected. Today I'm executing two queries each request. It's working fine, with desirable delay, but I'm wondering if it will be stable with more data.

- Single Date:
SELECT COUNT(*) from reservations WHERE created='DATE USER SELECTED'
SELECT COUNT(*), people from reservations WHERE created='DATE USER SELECTED' GROUP BY people

- Date Range:
 SELECT COUNT(*) from reservations WHERE created BETWEEN 'DATE USE SELECTED' AND 'DATE USE SELECTED';
 SELECT COUNT(*), people from reservations WHERE created BETWEEN 'DATE USE SELECTED' AND 'DATE USE SELECTED' GROUP BY people

IN MY VIEW
Pros: Real time statistics.
Cons: Can overload the database, with similar and slow queries.

I thought to create a secondary table, named 'statistics', and run a cronjob on my server, each morning, to calculate all statistics.

 - Table Example (statistics):

+----+------+--------------------+---------------------------+---------------------------+-----+
| ID | date | numberReservations | numberReservations2People | numberReservations3People | ... |
+----+------+--------------------+---------------------------+---------------------------+-----+

- IN MY VIEW
Pros: Faster queries, do not need to count every request.
Cons: Not real time statistics.

What you think about it? Theres a better approach?

Upvotes: 1

Views: 833

Answers (1)

O. Jones
O. Jones

Reputation: 108776

The aggregate queries you've shown can efficiently be satisfied if you have the correct compound index in your table. If you're not sure about compound indexes, you can read about them.

The index (created,people) on your reservations is the right one for both those queries. They both can be satisfied with an efficient index scan known as a loose range scan. You'll find that they are fast enough that you don't need to bother with a secondary table for the foreseeable future in your system.

That's good, because secondary tables like you propose are a common source of confusion and errors.

Upvotes: 1

Related Questions