Efficient schema to handle time interval queries on a huge table? We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table several times, resulting in a high delay on our queries.

This table have one particular property, the records are never updated after the insertion.

I see two solutions for this scenario, but I'm not sure which is better, and also which is recommended by database experts.


Optimize the query at maximum, trying to fetch everything in a single trip.
We can improve our database architecture to reduce the number of records, aggregating the old rows into auxiliar tables.


Example

My data look similar to a currency market, like this one: https://bitcoinity.org/markets. How they allow a quick query over different time intervals (minutes, hours, days, months, years...)?

Is there a well know solution for schemas like this one?

Background info


Ruby on Rails App;
MySQL
Few (or none) query optimization;
First rows dated from 2011.


Schema Details

Earnings

CREATE TABLE `earnings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `earner_id` int(11) DEFAULT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_earnings_on_sale_id` (`sale_id`),
  KEY `index_earnings_on_created_at` (`created_at`)
) ENGINE=Inno


Sample Query

SELECT DISTINCT *, count(amount) total FROM earnings
WHERE (created_at BETWEEN '2015-09-01 07:00:00' AND '2015-10-01 06:59:59')
  AND sale_id IN [....]
GROUP BY earner_id


Besides this query being very simple, it runs a lot of times for different timespan, like by month, or by the last 10 days. Thats why on my second idea (2.) I'm considering an aux table to cache the sums by each timespam. (see this example for the desired data aggregation https://bitcoinity.org/markets)
How many rows are being aggregated on each SELECT? 

This should go without saying - ensure you have an index on the datetime column. Depending on what your query is doing you can also try putting a compound index on your datetime and the column you're aggregating to try to only use the index and avoid reading data blocks off the disk.

Please post an EXPLAIN EXTENDED on your SELECT query along with a CREATE TABLE and if you are doing any JOINS then include the CREATE TABLE on those tables too. 

If you're using MyISAM switch to InnoDB for your table engine.

If your index is highly fragmented then you may want to consider partitioning your tables by date. If the index lookup isn't part of the problem then this won't help. 

If the output of these queries can be cached and subsequent requests would still be valid with that data then cache as much of it as you can.

Also ensure your my.cnf is configured for your usage patterns. 

As extra trivia, InnoDB benefits from having it's data files on fast storage and from high core speeds over more cores, especially when aggregation is being done.

[edit] Op posted SQL

You can likely drop a filesort by adding an ORDER BY NULL to the end of this query; 

Without knowing how many rows are being aggregated in the query nor the cardinality of the sale_id and created_at columns it's difficult to guess if a compound index gives better performance. You may want to test it.

E.g.; drop separate indexes and create a compound (sale_id, created_at):

ALTER TABLE earnings DROP INDEX index_earnings_on_sale_id, DROP INDEX index_earnings_on_created_at, ADD INDEX index_sale_id_created_at (sale_id, created_at);

In your example you are doing a COUNT(amount) instead of SUM(amount). If your goal is to only count earnings by earner_id and not sum them then you can also try doing the select in an inner query and counting on the outer. This can sometimes improve performance depending on the query.  

SELECT earner_id, COUNT(amount) AS total
FROM (
  SELECT earner_id, amount
  FROM earnings
  WHERE sale_id IN (428, 245) AND (created_at BETWEEN '2016-06-01 07:00:00' AND '2016-06-22 06:59:59')
) AS derived
 GROUP BY earner_id ORDER BY NULL