Reporting queries (I will use this term here) are the queries which summaries and groups data over the certain period of time. For example, in Social Network site we want to know how many messages have been sent for the given period of time, group by region and status (sent, received, etc), order by number of messages sent.
As an example I will take a table which is used to send SMS (text messages).
SQL: select concat('+', substring(region_code,1
,2), 'xxx') as reg, status, count(*) as cnt
from messages
where submition_date between '2009-01-01' and '2009-04-01' group
by reg, status
having cnt>100 order by cnt desc, status limit
100;
This query will do a range scan over the submition_date and perform a filesort. There are common well known approaches which can be used to optimize table (“covered index”, “summary tables”, using external data warehouse, etc). …
[Read more]