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.
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?
- Ruby on Rails App;
- Few (or none) query optimization;
- First rows dated from 2011.
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
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)