Fast query in MySQL with table of 1B+ rows

Question

Consider a MySQL table with the following columns:

source_id integer
timestep integer
position integer
value float

This table contains slightly more than 1,000,000,000 rows. The data is read-only and serves as source for certain analyses.

What can be done to speed up the queries?

1. I've created indices on all four columns. What else can be done?
2. The DB is currently on a MBP mid 2015, but it is going to be migrated to a workstation. Any suggestions on RAM-wise and / or SSD-wise?

Consider the following view on the previous table and a query on the view:

create view V(id, block, value) as
select source_id, round(timestep/25), avg(value) 
from T 
group by source_id, round(timestep/25);

select * from V where id=1;

The last query is taking ages to complete.

Version is 5.7.15

Answer 1

I would suggest that the round(timestep/25) is what is causing the slowdown in the group by part of the view.

Could you add a further field (timestep_aggregate) to the table containing the results of the round function? Using a trigger to populate the field on insert / update.

Or if using MySQL 5.7.5 or above, you could use a generated column which would do this for you - see Generated Columns in MySQL 5.7.5

If so, you could then create a combined index on source_id and timestep_aggregate, then use the new field instead of the function within your view. This would be much more performant.

Answer 2

You must provide the SELECTs you want help in speeding it up. One cannot design indexes without them. Meanwhile, VIEWs do not help with speed; they are just syntactic sugar. And, you must provide SHOW CREATE TABLE.

How many rows have source_id = 1? This composite index may speed up the SELECT (not necessarily the VIEW): INDEX(source_id, ,timestep, value).

Would Summary tables help?

PARTITIONing is unlikely to help for this query.

SSDs might help. How big is the table? What engine is it? How much RAM do you have?

"indices on all four columns" -- Individual indexes on each column is almost always a waste.

The slowest part of the query is fetching the rows, not the computations.

Answer 3

of course and SSD and more RAM (if proper adjust settings) could help

but 2 fundamental problems:

• you must understand how many records with each id, in Your case id = 1? it show You size of data which server operate
• round(timestep/25) - any function not use indexes

with problem #1 - SSD and RAM is solution with problem #2 - 2 other possible solutions:

• if MySQL less than 5.7 - add additional column round_step (indexed) and populate it with pre-calculated data
• if You can use MySQL 5.7 - use calculated columns with index over it

I not test pre-calculated columns on 1B rows tables, but use it on 50M+ work fine, so it must be tested