MySQL Database Design – Best Practices for Storing IP Addresses

You were more or less on the right track with (b), but you want to make sure that latitude and longitude are handled independently. What you need are indexes that cover your query parameters. This is what you are looking for:

1. Segregate your retailer addresses (including lat/lon). This could be in a RetailerAddress table or it could be columns on the RetailOutlet table, whichever makes the most sense to you.

2. Create two indexes: (lat), (lon) on whichever table contains your retail outlet addresses. If you have a separate table for retail outlet addresses, be sure to include the FK to the retail outlet in these indexes (i.e. (lat, OutletID), (lon, OutletID)).

Now as long as your query starts from the perspective of a particular user, this is all you'll need for a very efficient lookup on lat/lon ranges. If you need to find the proximate retail locations for a bunch of users at one time, then you will want to have similar indexes on the table containing user addresses.

First: there is no equivalent of the inet type of PostgreSql in MySQL yet.

You should just use the string type to store all kind of IP addresses. if you need them all in a certain format, convert them before inserting them into the DB.

If you need to do calculations with them, as you mentioned INET_ATON() and INET_NTOA() are the needed functions to be used.