I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc..
I'm having some issues avoiding
Using temporary and
Using filesort with some of the queries being run on a database.
Now, I know that sometimes this is unavoidable. Should I even bother changing it in this situation?
The application caches the requests that run the most of these queries, so they won't be run often at all. However for the sake of best practices and learning I want to make sure that I'm running things optimally.
Here's what's causing it. There's an assortment of tables containing various types of data. Assume a table structure where most of the columns are data that can be aggregated (SUM, AVG, etc) and any other columns are either ignored or grouped. One column consistent across the report data tables is a
DATE column. As the data is pulled daily from APIs, the data in each table is being stored for said date.
The majority of the front end components are currently displaying data to the end users at a monthly detail level (daily is needed though for upcoming features -- this level of detail cannot be compromised).
I'm currently using MySQL to group and aggregate data.
Here's a basic illustration:
SELECT DATE_FORMAT(c.date, '%Y-%m') as date, SUM(c.conversions) as conversions FROM data_ga_conversions c WHERE c.goal_id = 1 AND c.date BETWEEN '2014-10-01' AND '2015-02-28' GROUP BY YEAR(c.date), MONTH(c.date)
GROUP BY clause using date functions. As I'm sure you know -- this is what's causing the query executor to use temporary and filesort. (Note: I'm aware that I can get rid of filesort by adding
ORDER BY NULL, however I'm trying to keep these basic sorting and aggregation calculations in one place, application code or SQL.)
Am I approaching this the wrong way? Here's a couple of alternate methods I had considered:
- Storing year, month and day in separate columns.
- Aggregate tables (à la materialized views... switching to postgresql is a potential option too).
- Let the application do it