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