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

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(at)svana(dot)org> 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(at)svana(dot)org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
>
>

--
http://preferans.de