Hi,
I am making application for vehicle tracking system using GPS. There I am supposed to receive data every minute from a device.
Technically - I will be getting 5 records every minute per device.
i.e 5 * 60 * 24 records/day = 7200 records/day for single device.

I am suppose to store that data for 2 months
i.e 7200 * 30 * 2 = 4,32,000 records for a single device.

If number of devices are 100 then storing all the records (43200000 records) in single table wont be a feasible option, as i need to fetch records from the table for report generation.

Can any one help to come over the above mentioned problem?

Thanks,
Rahul

There is no problem storing 43200000 rows in a single table.

When designing the data model look closely at what you will be doing with the data rather than the number of records in the table. For instance, what types of reports will you be running? Will it really need access to all this information? On which keys will it be accessing the information? Can information be summarized and called upon at a later point? What about data management of data, will you need to delete a months worth of data at a time?

On the reporting requirements you will need to get this information to determine the indexes and the types of indexes to be created on each of the tables. For managing large amounts of data within the table have a look at partitions.

Thanks Ronan for the inputs. Table majorly will have the column as deviceid (primary), latitude, longitude, time stamp, date. Report will be generated
1) as where was the vehicle was in the past at a specific time (query as per device id, time stamp, date).
2) What was the route followed by vehicle on a specific day etc (number of queries as per device id, time stamp, date).

Indexing will surely be done (on device id). But when user demands for the report, still there will be ample data in the table when the query will be fired, which might result in performance issue.
Our initial thought to this problem is to create table for every device. So when there are 100 device there will be 100 table to cater the need. Or we can make partition where 1 table will have 20-25 device info. In such a case our initial thought are that our problem can be solved. Need your inputs over the same Or any better solution for the same.

Also for the data - it will be archived every 2 months. Anyways I will be storing 2 month data, which is also good in number.

Awaiting reply...

Thanks,
Rahul

It sounds like partitioning your table will help you with performance. Have a read of the following link which highlights the performance improvements while using partitions for large volumes of data: MySQL :: Improving Database Performance with Partitioning