3

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.

  1. Optimize the query at maximum, trying to fetch everything in a single trip.
  2. 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)

  • Give us a SHOW CREATE TABLE My_Table\G on your table. Show us the text of your querie(s)? – Vérace Jun 25 '16 at 22:49
  • Done, hope this help :) – Peoplee Jun 26 '16 at 1:10
  • And, as @BillCroft says, the EXPLAIN EXTENDED. – Vérace Jun 26 '16 at 1:20
  • From a query that covers a lot of data? – Vérace Jun 26 '16 at 1:28
  • Also, what OS are you running? Your HDD/RAID config? Your RAM and CPU? If Linux, can you give us the out put of iostat and vmstat while running a problem query? Also, could you post some sample (historical maybe if there are data confidentiality issues) data to pastebin or similar? – Vérace Jun 26 '16 at 1:29
1

How they allow a quick query over different time intervals (minutes, hours, days, months, years...)?

The trick is not to do it at runtime at all. If you think this is done by standard SQL "select when needed", you err.

  • Data is generally aggregated when coming in, with the aggregated rows written to separate tables as needed (minute, hour etc.).

  • Charts are prepared from in memory copies of the data, particularly if you show "from current to the last X" bars type of queries. It is simply not efficient to ask the database over and over. You log for regeneration, but you keep the most needed data in memory.

You do not even need to store tick data in the database at all - just aggregates. At least this is what I do - the rare case I need tick data, i go back and parse a binary coded file.

Surely you can use a standard SQL approach, but then expect to pay significantly for inferior performance. Time series aggregation is a very specific scenario.

1

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

  • Thank you for the tips, I'm already using InnoDB and I started reading about partitioning right now, seems that it will help me alot but it will be my first time using it. Let's see... o/ – Peoplee Jun 26 '16 at 1:15
  • 1
    @Peoplee the partitioning will only help with performance in very specific cases, it is a lot more complex that it seems at first. It is very important to start with the EXPLAIN to see what is really happening and what changes for different time spans. – jkavalik Jun 26 '16 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.