I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained database (DND), which currently holds 231450000 mobile numbers.
My question is
- What is the efficient way to store this data (DND).
- What is the efficient way to check, whether the number exists within these numbers or not.
- Twice in a week, a delta dump of these numbers (DND) is provided by telecom authority. How to sync the dump with existing db.
- Should I use mysql to store this data, which will consist of mobile numbers, or any other in-memory storage?
- Each row of data consists of mobile number alongwith few preferences like area code, user preference etc
- The data dump provided twice a week contains only additions / deletions and not the whole data.
- Lookup frequency can be very large. Actually every SMS sent from the platform has to first make a lookup against the data (DND). So if the platform is handling 100 sms per second, it has to make 100 lookups per second against this data. The lookup simply checks existence of a particular mobile number against the data(DND) in database.
- The new delta data dump (additions / deletions), has to be synced in few hours only, as the dump is provided by telecom authority in the day and the same has to go live by midnight 00:00 hours. So the total time to sync data will be around 3 hours.
Sample DND dump
Sample of DND dump provided. Here
Opstype means record has to be added, while as
D means record has to be deleted.