search

julien-nc / phonetrack

Nextcloud app to create tracking sessions to display phones position in real time and export activity to gpx.
GNU Affero General Public License v3.0
47 stars 6 forks source link

Reorder OwnTracks friends SQL query for improved performance #14

Closed mbirth closed 3 months ago

mbirth commented 10 months ago

Previous query joined the whole of _points to _devices before filtering. That one took between 10 and 16 seconds for me. (2.5m points in an SQLite database) This caused OwnTracks to fail to log in the background most of the time.

Moved the JOIN to the outer query which reduced the time to 3 seconds.

While this will now first find the latest points for ALL devices and filter them afterwards for the specified sessionid, it works a lot faster for me because it can make use of the indexes.

julien-nc commented 3 months ago

Thanks a lot and sorry for the delay.