Summary report query

Hello guys,

I am trying to build an internal app like a dashboard to do some BI analysis and querying on the database. The problem is the timeout for the query below always exceed its duration, making it lose the connection returning following error:

Error Code: 2013. Lost connection to MySQL server during query 30.000 sec

However, the query works if I filter only 3 devices, during 29 secs. It returns the average speed for each device in a certain period of time.

How does Traccar do it behind the scenes to show it on the web interface? The summary reports shows up for this period without any problem, but querying direct on the database does not work.

Does anyone have an advice for this application?

SELECT tc_devices.name as 'Name', AVG(tc_positions.speed) as 'Speed'
FROM tc_devices
INNER JOIN tc_positions
ON tc_positions.deviceid = tc_devices.id 
WHERE tc_positions.devicetime BETWEEN '2018-09-01 00:00:00'
AND ('2018-09-15 00:00:00')
AND tc_devices.id = 33
OR  tc_devices.id = 34
OR  tc_devices.id = 35
OR  tc_devices.id = 75
OR  tc_devices.id = 120
OR  tc_devices.id = 121
OR  tc_devices.id = 122
OR  tc_devices.id = 123
OR  tc_devices.id = 124
OR  tc_devices.id = 125
OR  tc_devices.id = 111
OR  tc_devices.id = 110
OR  tc_devices.id = 109
OR  tc_devices.id = 127
OR  tc_devices.id = 112
OR  tc_devices.id = 113
OR  tc_devices.id = 114
GROUP BY tc_devices.id

try increase your query timeout in Mysql setting.

Wouldn't it be harmful to the database machine?

Lucas, please no all-upper-case titles in future. Be respectful to others.