Search.pm SQL queries

[Joel Peshkin]

Michael,

  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.

-Joel Peshkin


Michael Leupold wrote:
> 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
> -
> To view or change your list settings, click here:
> <http://bugzilla.org/cgi-bin/mj_wwwusr?user=bugreport@peshkin.net>
>