Search.pm SQL queries

[Michael Leupold]

Hi,

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.

I think this could be rewritten as:
INNER JOIN longdescs AS ld ON ld.bug_id = bugs.bug_id INNER JOIN profiles AS 
pf ON pf.userid = ld.who AND $$term

or even as:
INNER JOIN longdescs AS ld ON ld.bug_id = bugs.bug_id INNER JOIN profiles AS 
pf ON pf.userid = ld.who WHERE $$term
(which would provide the best separation and be easiest to work with).

It seems that the second and third query perform better (on MySQL 5.1).

As I said I'm not an expert on databases so I'd like to get some ideas from 
people who know more than I do.

Regards,
Michael