php - MySQL: How to query multiple 'statistics' in a single query? -

this more of question rather problem need solve. backend fast , queries running great, it's not that important. okay, let's it.

i have 4 panels of statistics on dashboard regarding number of views today, yesterday, week , month; each taking 1 query in database. wondering is, how 1 put queries ease load on database/server?

i looking through stackoverflow before asking , saw 1 saying like:

sum(case when status = 'open' 1 else 0 end) [open], sum(case when status = 'closed' 1 else 0 end) [closed] 

source: gathering multiple statistics table in single query

which need, like:

sum(case when date(created_at) = '2015-07-23' 1 else 0 end) today, sum(case when date(created_at) = '2015-07-22' 1 else 0 end) yesterday, sum(case when week(created_at) = '29' 1 else 0 end) week, sum(case when month(created_at) = '7' 1 else 0 end) month 

i wondering if has better suggestions, have applied function , works fine.

the comments giving hints. i'll provide here idea, conceptually used in of banks i've been working with.

when there billions of rows, , not need exact instant snapshots every request (meaning: have tolerance outdated data), worth exploring batch processes.

this how works:

1. you define lag tolerance: example: "i'm ok data outdated 8 hours". periodicity of batch process.
2. you denormalize database add "redundant" column/table storing running totals of choice. example, add table called statistics_snapshot 4 columns: (timestamp, month, day, week) or of like.
3. you create stored procedure in mysql fills table 4 queries, or 1 global queries suggested. timestamp registered know when taken.

4. you create user execute grant procedure, only.

   create user 'cron_mysql_user'@'localhost' identified 'strongpassword'; grant execute on procedure db_name.proc_name 'cron_mysql_user'@'localhost'; 

5. you use dbms_job cron job connect mysql , run procedure periodicity defined in point #1. can run scripts command line this:

   mysql --user='cron_mysql_user'@'localhost' -pstrongpassword --execute="call proc_name()" db_name 

6. you create nice reports based on periodic snapshots :-)

the advantage of doing centralized i/o few times day only, controlled manner, have light select statement when need know statistics.