Fast query in MySQL with table of 1B+ rows 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?


I've created indices on all four columns. What else can be done? 
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
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.