558336 - What's up? query does table scan

Status: RESOLVED DUPLICATE of bug 824375

(Bugzilla :: Query/Bug List, enhancement)

Description

[Piotr Tarnowski]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; pl-PL; rv:1.9.0.18) Gecko/2010021501 Ubuntu/8.04 (hardy) Firefox/3.0.18
Build Identifier: version 3.4.6+

I found it useful to search bugs based on their last change attribute (this is what's up? query - bugs changed in last two days, or bugs which need attention). This search results in folowing SQL phrase (for mySQL): 
((TO_DAYS(NOW()) - TO_DAYS(bugs.delta_ts)) < '2') 
which is hard to be supported by database index, especially in mySQL where there is no functional indexes.




Reproducible: Always

Steps to Reproduce:
1. https://bugzilla.mozilla.org/buglist.cgi?debug=1&query_format=advanced&field0-0-0=days_elapsed&type0-0-0=lessthan&value0-0-0=2

Actual Results:  
((TO_DAYS(NOW()) - TO_DAYS(bugs.delta_ts)) < '2') 
which is not supported by index on mysql

Expected Results:  
((bugs.c_delta_ts_days) < ('2' - TO_DAYS(NOW()))
which can be supported by index

I developed and implemented workaround which speeds up delta_ts based queries:
1. On database level (mySQL only, postgresql has functional indexes which make it easier):
ALTER TABLE bugs ADD(c_delta_ts_days INT);
CREATE INDEX i_bugs_delta_ts_days ON bugs(c_delta_ts_days);
CREATE TRIGGER tr_bu_bugs BEFORE UPDATE ON bugs 
    FOR EACH ROW SET NEW.c_delta_ts_days = 0 - to_days(NEW.delta_ts);
CREATE TRIGGER tr_bi_bugs BEFORE INSERT ON bugs
    FOR EACH ROW SET NEW.c_delta_ts_days = 0 - to_days(NEW.delta_ts);
UPDATE bugs SET c_delta_ts_days = 0 - to_days(delta_ts);

2. Use new column in search (patch against 2.22 but similar problem seams to be in 3.4.6:

+++ /usr/share/perl5/Bugzilla/Search.pm (working copy)
@@ -887,6 +887,14 @@
                                "ON $flags.setter_id = setters_$chartid.userid");
              $f = "setters_$chartid.login_name";
          },
+
+         # use indexed function field c_delta_ts_days in some cases
+         "^(changedin|days_elapsed),(?:equals|notequals|greaterthan|lessthan),"
+         => sub {
+           $f = 'bugs.c_delta_ts_days';
+           $q = "(" . $q . " - " . $dbh->sql_to_days('NOW()') . ")";
+         },
+

          "^(changedin|days_elapsed)," => sub {
              $f = "(" . $dbh->sql_to_days('NOW()') . " - " .

Comment 1

[Max Kanat-Alexander]

  Thanks for the report, Piotr! If you'd like to get your patch into Bugzilla, see our development process here:

  http://wiki.mozilla.org/Bugzilla:Developers

Comment 2

[Frédéric Buclin]

Bug 824375 has a better description of the problem.