From: | "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> |
---|---|
To: | "PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: match an IP address |
Date: | 2008-09-23 00:06:16 |
Message-ID: | e373d31e0809221706s12986fe4k5d92bf1f2f2f02c7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> > 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tino Wildenhain | 2008-09-23 00:19:23 | Re: match an IP address |
Previous Message | Tino Wildenhain | 2008-09-22 21:34:50 | Re: match an IP address |