I have 3 tables:
Device: Id, Name.
Machine: Id, P1, P2, date_time, IdDevice.
Parameters: Id, T, V, I, date_time, IdDevice.
In device there isn't a lot of records. But in the other way, inside Machine and Parameters every minute is inserted a new record. So right now we can find 1M records and the next year we expect 20M records inside of each of this 2 tables.
IdDevice and date_time for both tables are indexed.
How can I improve the performance?
For example for this sentance it's taking 10 min and it isn't done yet.
SELECT p.T, p.V, p.I, m.P1, m.P2, p.date_time FROM Device d INNER JOIN Parameters p ON d.ID = p.IdDevice INNER JOIN Machine m ON d.ID = m.IdDevice GROUP BY d.ID ORDER BY p.date_time DESC