3 ms·
You could store the IP as a 32 bit unsigned int. There's no issue with that, but I would probably recommend nowadays to use an internal cidr or inet type if you
by pg_bot 5y ago
You could store the IP as a 32 bit unsigned int. There's no issue with that, but I would probably recommend nowadays to use an internal cidr or inet type if your database supports that. It probably wouldn't be better to split the email address from the domain for filtering since email address formats tend to be a bit tricky.
Joins give a performance advantage due to the fact that you aren't duplicating data unnecessarily. The slow query in question becomes five queries (4 for the data once for the final lookup) which can each be done quickly and if any one of them return nil, you can return a timeout.
- rmk 5y agoYes, some of the databases support native ip address types that can be manipulated efficiently. Better to use that (like inbuilt json querying capabilities in postgres) than come up with your own. It is still not clear how is it better to do up to 5 separate queries or maybe a few joins, than to store the strings and construct indices and such on them? Is the idea that the cost of possibly establishing a new socket connection for some concurrent query execution or sequentially executing 5 queries is still < possible scans of the columns (even with indexing in effect)? Also, even if you had integers, don't you need some sort of an index on integer fields to avoid full table scans anyway?