Phoenix Kiula wrote:
my IP addresses are stored in a TEXT type field. that field can actually contain usernames like 'joao' or 'scott' and it can contain IP addresses....I think this is common DB design on many websites that have registered user IDs.
Is it? Name one! Sounds like crappy design to me.
My humble suggestion would be to make another column in the table called "user_registered" or something. Make this an int(1). If the user is registered, value is 1, otherwise 0.
Maybe "registration" (aka authentication) is mixed up with simple session handling?
To update your existing data onetime, run an SQL update query looking for IP pattern with 3 dots (which is likely IP address and not user id) or by the INET conditions in previous suggestions.
It could also just be a fdn (hostname) - still I'm a bit puzzled how a username and an IP-address can get into the same field.
Next, include the columns "user_id" and "user_registered" in the same index. This won't increase the size of the index too much, as the user_registered field is a small INT number. But from now on your SQL queries: ...WHERE user_id = 'testuser' AND user_registered = 1 will return much faster. I have found this to the most convenient and fastest solution in PGSQL instead of haivng INET in the WHERE clauses.
Can you give example on where the inet entry is going to be used? Cheers Tino
smime.p7s
Description: S/MIME Cryptographic Signature
