I am a making a car tracking system and i want to store data that each car sends after every 5 seconds in a MySql database. Assuming that i have 1000 cars transmitting data to my system after 5 seconds, and the data is stored in one table. At some point i would want to query this table to generate reports for specific vehicle. I am confused between logging all the vehicles data in one table or creating a table for each vehicle (1000 tables). Which is more efficient?
3 Answers
0
votes
OK 86400 seconds per day / 5 = 17280 records per car and day. Will result in 17,280,000 records per day. This is not an issue for MYSQL in general. And a good designed table will be easy to query. If you go for one table for each car - what is, when there will be 2000 cars in future. But the question is also: how long do you like to store the data? It is easy to calculate when your database is 200 GB, 800GB, 2TB,....
0
votes
One table, not one table per car. A database with 1000 tables will be a dumpster fire when you try to back it up or maintain it.
Keep the rows of that table as short as you possibly can; it will have many records.
Index that table both on timestamp and on (car_id, timestamp) . The second index will allow you to report on individual cars efficiently.
0
votes
This is the "tip of the iceberg". There are about 5 threads here and on dba.stackexchange relating to tracking cars/trucks. Here are some further tips.
- Keep datatypes as small as possible. Your table(s) will become huge -- threatening to overflow the disk, and slowing down queries due to "bulky rows mean that fewer rows can be cached in RAM".
- Do you keep the "same" info for a car that is sitting idle overnight? Think of how much disk space this is taking.
- If you are using HDD disks, plain on 100
INSERTs/second before you need to do some redesign of the ingestion process. (1000/sec for SSDs.) There are techniques that can give you 10x, maybe 100x, but you must apply them. - Will you be having several servers collecting the data, then doing simple inserts into the database? My point is that that may be your first bottleneck.
PRIMARY KEY(car_id, ...)so that accessing data for one car is efficient.- Today, you say the data will be kept forever. But have you computed how big your disk will need to be?
- One way to shrink the data drastically is to consolidate "old" data into, say, 1-minute intervals after, say, one month. Start thinking about what you want to keep. For example: min/max/avg speed, not just instantaneous speed. Have an extra record when any significant change occurs (engine on; engine off; airbag deployed; etc)
- (I probably have more tips.)
