Count : 11 (0.11%) Time : 906 s total, 82.363636 s avg, 41 s to 128 s max (0.52%) 95% of Time : 778 s total, 77.8 s avg, 41 s to 116 s max Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%) 95% of Lock : 0 total, 0 avg, 0 to 0 max Rows sent : 1 avg, 1 to 1 max (0.00%) Rows examined : 18.57M avg, 18.49M to 18.73M max (0.99%) Database : beaker Users : beaker 127.0.0.1 : 100.00% (11) of query, 100.00% (10029) of all users Query abstract: SELECT COUNT(N) AS count_1 FROM activity INNER JOIN system_activity ON activity.id = system_activity.id; Query sample: SELECT count(1) AS count_1 FROM activity INNER JOIN system_activity ON activity.id = system_activity.id;
This query is being issued by turbogears.paginate module. It does a count() before any pagination (i.e LIMIT) clauses are added to the query. As can be seen, this query does not run all that often (total of 11 times) but it is very very slow. Perhaps we could look at always requiring some kind of filter for this page.
I would prefer to just skip showing a total number of rows or total number of pages in this case. So we avoid the COUNT() query, but users aren't forced to search before they can see anything.
Actually this will be quite difficult to fix without either patching TurboGears (or forking TG @paginate into Beaker and then modifying it). In its current incarnation, there is no way to tell @paginate to avoid issuing the COUNT() query.
Having ported the activity pages to use Backgrid, this patch removes the count from those grids which are likely to accumulate a very large number of rows: http://gerrit.beaker-project.org/4022
Steps to reproduce: 1. Open the system activity page (Activity -> System from the menu). Expected results: The page should load in a reasonable amount of time (< 10 seconds). The total number of results found will *not* be shown on this page, because calculating it is too expensive (that's the bug). Suggested test cases: We have ported all the activity pages to use Backgrid now, with different code for searching and for producing the grid data. We have addresses all the poorly performing queries that we could find, which included this one. It should be possible to open all the different activity pages and browse through them and perform simple searches, with the results loaded in a reasonable amount of time. Some searches will be slower than others but none should take long enough to enter the slow query log (> 10 seconds).
Beaker 20.0 has been released.