MySQL queries to generate table statistics -

MySQL queries to generate table statistics -

i have mysql database table have around 10-15k inserts daily, , increment next months.

- table illustration (reservations): *important fields* +----+--------+----------+---------+-----+ | id | people | modified | created | ... | +----+--------+----------+---------+-----+

i need provide daily statistics, informing how much entries had (total , specified same number of people), based on date or date range user selected. today i'm executing 2 queries each request. it's working fine, desirable delay, i'm wondering if stable more data.

- single date: select count(*) reservations created='date user selected' select count(*), people reservations created='date user selected' grouping people - date range: select count(*) reservations created between 'date utilize selected' , 'date utilize selected'; select count(*), people reservations created between 'date utilize selected' , 'date utilize selected' grouping people in view pros: real time statistics. cons: can overload database, similar , slow queries.

i thought create secondary table, named 'statistics', , run cronjob on server, each morning, calculate statistics.

- table illustration (statistics): +----+------+--------------------+---------------------------+---------------------------+-----+ | id | date | numberreservations | numberreservations2people | numberreservations3people | ... | +----+------+--------------------+---------------------------+---------------------------+-----+ - in view pros: faster queries, not need count every request. cons: not real time statistics.

what think it? theres improve approach?

the aggregate queries you've shown can efficiently satisfied if have right compound index in table. if you're not sure compound indexes, can read them.

the index (created,people) on reservations right 1 both queries. both can satisfied efficient index scan known loose range scan. you'll find fast plenty don't need bother secondary table foreseeable future in system.

that's good, because secondary tables propose mutual source of confusion , errors.

mysql