632718 - Only return 500 results unless the user specifically requests to see more

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, enhancement, P1)

Description

[Max Kanat-Alexander]

Time spent in searching the database can be broken down into basically these things:

1) The query analysis and parsing done by the database engine.
2) The actual locating of data using the parsed query.
3) The actual delivery of data from the database to Bugzilla.
4) The rendering of the results.

Of these, it's 3+4 that actually take most of the time--1+2 are usually almost instantaneous.

Most people rarely need to see more than 500 results in a search, at the most.

So, to speed up searches, we can, by default, limit every search to 500 results. Then if there are more results (which we can know by doing an actual database-side limit of 501) we can provide a link on the buglist that will re-run the query and have it display *all* the results (but subject to the limit set by bug 632717).

Comment 1

[Max Kanat-Alexander]

This will only affect the HTML interface--all programmatic interfaces will still always return the full results.

Comment 2

[Max Kanat-Alexander]

Attachment: v1

So, here's a patch that does this, but it doesn't seem to actually help our performance. Since we have an ORDER BY clause (and a GROUP BY clause) on our SQL, the database runs the whole search before taking LIMIT into account.

Comment 3

[Max Kanat-Alexander]

Oh, I guess it does still help in many cases, actually:

  http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

Comment 4

[Max Kanat-Alexander]

Attachment: v2

Okay, further testing with a slightly different configuration proves that this does in fact have significant performance benefits. (My previous tests were with columns like remaining_time, which are always slow.)

I can sign off on the Search.pm side of things here, but pyrzak, could you sign off on the UI?

Comment 5

[Max Kanat-Alexander]

Attachment: UI for a limited search

Here's a screenshot of what happens when you do a search and it gets limited. The same link appears at the bottom of the list, as well.

Comment 6

[Max Kanat-Alexander]

Comment on attachment 513843 [details] [diff] [review]
v2

Sweet, thanks Guy!

dkl, do you want to sign off on all the stuff here that isn't in Search.pm?

Comment 7

[David Lawrence [:dkl]]

Comment on attachment 513843 [details] [diff] [review]
v2

I feel like we should still return the number of bug found in addition to the new show all link. With the patch, it just show the 500 limit message and for any number of bugs less than 500. People may still like to know how many that was returned.

Maybe the following patch would be better:

+[% BLOCK num_results %]
+  <span class="bz_result_count">
+    [% IF bugs.size == 0 %]
+      <span class="zero_results">[% terms.zeroSearchResults %].</span>
+    [% ELSE %]
+      [% IF bugs.size == 1 %]
+        One [% terms.bug %] found.
+      [% ELSE %]
+        [% bugs.size %] [%+ terms.bugs %] found.
+      [% END %]
+      [% IF default_limited %]
+        This result was limited to [% Param('default_search_limit') FILTER html %]
+        [%+ terms.bugs %].
+        <a href="buglist.cgi?[% urlquerypart FILTER html %]
+              [%- "&order=$qorder" FILTER html IF order %]&limit=0">See
+          all search results for this query</a>.
+      [% END %]
+    [% END %]
+  </span>
+[% END %]

Comment 8

[Max Kanat-Alexander]

Comment on attachment 513843 [details] [diff] [review]
v2

(In reply to comment #7)
> I feel like we should still return the number of bug found in addition to the
> new show all link.

  Oh, there are only 500 bugs returned, we're doing a LIMIT clause. If we were to add SQL_CALC_FOUND_ROWS, that removes all performance benefits of LIMIT, as MySQL actually goes on to calculate the entire result regardless oflimit.

Comment 9

[Alex Eiser]

(In reply to comment #8)
>   Oh, there are only 500 bugs returned, we're doing a LIMIT clause. If we were
> to add SQL_CALC_FOUND_ROWS, that removes all performance benefits of LIMIT, as
> MySQL actually goes on to calculate the entire result regardless oflimit.

You will still get a lot of user performance boost by not transferring the data... and some boost from mysql because it won't need to sort/process all the data.

But not as good as not doing the work at all of course.

Comment 10

[David Lawrence [:dkl]]

Hmm, not sure you understood what I was asking. I was just merely stating to still show bugs.size in the template output on the buglist.cgi page. You shouldn't need to do a call to the DB for that as the template is just displaying the number of entries in the $vars->{bugs} Perl list.

Dave

Comment 11

[Max Kanat-Alexander]

(In reply to comment #9)
> You will still get a lot of user performance boost by not transferring the
> data...

  MySQL will still pull every row from the database (although it still does that in many cases anyhow, since we have a GROUP BY).

> and some boost from mysql because it won't need to sort/process all the
> data.

  No, bypassing the LIMIT optimization would mean sorting every row:

  http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

Comment 12

[Max Kanat-Alexander]

(In reply to comment #10)
> Hmm, not sure you understood what I was asking. I was just merely stating to
> still show bugs.size in the template output on the buglist.cgi page.

  Oh, bugs.size is 500...or it should be. Oh right, I only check default_limited! I will fix that. Gee. :-)

Comment 13

[Max Kanat-Alexander]

Attachment: v3

Now we only display the "500" message if there were exactly 500 results.

Comment 14

[David Lawrence [:dkl]]

Comment on attachment 514350 [details] [diff] [review]
v3

Looks good now. I did test the patch but I do not have a database with that many results locally. If your testing on a large amount of results works then r=dkl.

Comment 15

[Max Kanat-Alexander]

Sweet, thank you for the review, dkl! :-)

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/trunk/                       
modified buglist.cgi
modified Bugzilla/Search.pm
modified Bugzilla/Config/Query.pm
modified template/en/default/admin/params/query.html.tmpl
modified template/en/default/list/list.html.tmpl
Committed revision 7734.