Re: match an IP address

Phoenix Kiula wrote:

> Ever tried this crap on a table of 10 million records on a live
> website, where this query is happening at 3000 times per second? No
> such function schtick will match the raw speed of a simpler indexed
> query. Or did you mean my index should contain the COALESCE already?

Hmm. My previous response may have been overly grumpy.

The point I was *trying* to make is that shoving a username/id and an IP
address into a single field is probably not ideal. At least in my
experience you pay for this sort of optimisation (if it even works out
as an optimisation in the first place) down the track. I have the
misfortunate to have to administrate a system full of such multi-use
fields, and have developed a real loathing for the approach.

If you don't want to store IPs for registered users, I'd use:

user_id INTEGER,
ip cidr,
CONSTRAINT must_have_userstamp
CHECK ( user_id IS NOT NULL OR ip IS NOT NULL)

... and yes, I'd use a functional index to look it up, or even a
trigger-maintained cache of the text representation if I had to. Then
again, I guess I'm lucky enough to work in environments where data
integrity and correctness is a priority and the resources available are
a good fit to the tasks the database needs to do.

--
Craig Ringe