Hope this question doesn’t get downvotes as it is little theoretical and it is a feature enabled in my application which is of real time racing appliccation.
In this application admin creates races and set some route and racers play those races with their mobile as they must have our product app.
Now what I am going to explain is we have created our own tracking feature for our admins in which admin can see the current positions of all the racers and for that we have done this implementation
there was a api which recieves user location in every 2-3 seconds and those records are stored in Database
(mysql database,INNODB)
and these are the field which i stored
{
"latitude":"",
"longitude":"",
"timestamp":""
"bearingAngle":"",
"currentSpeed":""
}
in my location table and for currentlocation i used to query my users latest location according to time and from that i was able to create current location tracking
one more feature which i introduced was complete tracing of users in which i use all the locations stored in DB and create a KML (Keyhole markup language) file by which complete track is visible to admin which racer has used for that race.
This was working fine but when the races increased the DB load gradually increased and day after day my tracking performance decreased as there were so much data and querying that data my db performance went down ….
For that i switched my implementations and instead of storing location data into DB
i stored those location data in txt files in this format
latitude|longitude|timestamp|bearingAngle|currentSpeed
latitude|longitude|timestamp|bearingAngle|currentSpeed
latitude|longitude|timestamp|bearingAngle|currentSpeed
latitude|longitude|timestamp|bearingAngle|currentSpeed
.
.
.
.
.
and stored only the latest location on table
from now i can get the current location from the tables and for tracing part i can use my files
this implementation reduced my load on DB and my performance also improved on backend level
Now there is no such code to debug but it is a architecture which i want to create and also created and at my own I did my best to optimize this implementation but somewhere in my mind there is some doubt that might be this implementation can be done more efficiently and I just want some ideas that how can i achieve this implementation more better.
Hope my problem is understood and again I am repeating this is not code level issue it is just a implentation improvement.
Thank you !!!