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
ORDER BY p.date_time DESC 
  • 2
    Please consider reading this advice – mustaccio Sep 24 '19 at 14:59
  • Include the EXPLAIN QUERY PLAN output? Also run ANALYZE if you haven't, and maybe a PRAGMA optimize daily after that to rebuild the stats tables as needed as the DB grows. – Shawn Sep 24 '19 at 16:40
  • 1
    Also what's with that GROUP BY? It'd cause an error on most other databases because you have non-grouped, non-aggregate columns in the select... As it is, sqlite just picks a random row from the group to use instead of erroring out. See sqlite.org/… – Shawn Sep 24 '19 at 16:44

Your Answer

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

Browse other questions tagged or ask your own question.