Search.pm SQL queries

[Michael Leupold]

On Wednesday 25 February 2009 14:28:49 Joel Peshkin wrote:
> > I've recently been scouting around Search.pm, trying to do something
> > about https://bugzilla.mozilla.org/show_bug.cgi?id=476722 (no promises
> > something will come out).
> >
> > I stumbled upon some queries where I'm not entirely sure why they are
> > implemented like that, eg. _commenter:
> > ---------------
> >     $$f = "login_name";
> >     $$ff = "profiles.login_name";
> >     $$funcsbykey{",$$t"}($self, %func_args);
> >     push(@$supptables, "LEFT JOIN longdescs AS $table " .
> >                        "ON $table.bug_id = bugs.bug_id $extra " .
> >                        "AND $table.who IN" .
> >                        "(SELECT userid FROM profiles WHERE $$term)"
> >                        );
> >     $$term = "$table.who IS NOT NULL";
> > ---------------
> >
> > I don't really understand why there's a LEFT JOIN followed by a "who IS
> > NOT NULL" which seems to be the same as an INNER JOIN. Not being a
> > database guru I'm also not sure if the subquery is supposed to perform
> > faster than another join.
>   Boolean charts surround entire WHERE clauses with operators including
> NOT( ).  Add the debug option to some queries where you look for a bug
> assigned to someone who has never commented and look at the resulting SQL.
>   That said, the logic goes back to MySQL 3 days.   There may be
> opportunities to optimize more with subqueries.

Thanks for the pointer. I really didn't consider negation at all.

That said it seems the subquery is pretty slow for me. The following seems to 
run considerably faster while providing the same functionality:
LEFT JOIN (longdescs
INNER JOIN profiles
ON longdescs.who = profiles.userid
AND $$term)
ON longdescs.bug_id = bugs.bug_id
WHERE longdescs.who IS NOT NULL

I only tried this on MySQL 5.1 as I don't have a postgres bugzilla database 
around currently.
Unfortunately like this it's still not possible to move the actual query 
condition $term into a WHERE clause which is what I tried to achieve in the 
first place.

Regards,
Michael

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
URL: <http://lists.bugzilla.org/pipermail/developers/attachments/20090225/c87ede69/attachment.sig>