> > 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.
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.
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.
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.
