Re: Optimizing query: select ... where id = 4 and md5(...) = '...'

On Mon, Jul 03, 2006 at 03:13:15PM +0200, Alexander Farber wrote:
> Hello,
>
> in my application I'm trying to authenticate users
> against a table called "users". The integer column
> "id" should match, but also an md5 hash of the
> "password" column (salted with a string) should match.
> My authentication function (written in C, using libpq)
> should return a "username" (is a varchar(200) field).
>
> I wonder, what is faster: fetching 2 columns - the
> username and the md5-result and then comparing the
> md5 string against the argument in my app, like here:

I don't know about speed, but I think the choice should really be based
on whether you want to be able to tell the difference between unknown
user and bad password. You can still do the comparison in the database
by doing something like:

select username, md5('deadbeef' || password) = 'blah'
from users where id = 4;

So the second field will be true or false.

In any case, the testing you're doing is bogus, since you're probably
testing backend startup time as well, which is probably longer than the
query you're running anyway. Even then, 5ms for the whole process is
not to be sneezed at.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.