Yes, you're probably right.
I'm just trying to ensure, that the slow md5() function isn't
called for every row in the table. If that's not the case, then
the other tiny speed differences are not that important for me.
Your query works too, thanks for the hint.
punbb=> select username, md5('deadbeef' || password) = 'blah' from
users where id = 4;
username | ?column?
----------+----------
Vasja | f
(1 row)
punbb=> explain select username, md5('deadbeef' || password) = 'blah'
from users where id = 4;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using users_pkey on users (cost=0.00..5.95 rows=1 width=156)
Index Cond: (id = 4)
(2 rows)
Regards
Alex
On 7/3/06, Martijn van Oosterhout <kleptog@xxxxxxxxx> wrote:
On Mon, Jul 03, 2006 at 03:13:15PM +0200, Alexander Farber wrote:
> 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.
--
Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
--
http://preferans.de
