Version 1.04 significantly slower than 1.01

[albertino13]

is there a reason wyh version 1.04 is significantly slower than version 1.01.

It takes more than 10 sec. to display 3000 bugs from the project overview. In#
version 1.01 this happens almost instantly.

As well queries do last more than usually.

Are there any possible bottlenecks?

using a standard configuration with ubuntu, apache webserver with mysql 5.

[Bryce Nesbitt]

Enable your mysql "slow query log" --- and post the results --- to get help.

phpBugTracker depends on the mysql query cache for speed.
There is no known reason that 1.04 would be slower than 1.01 -- nothing fundamental changed.

1.05 should be faster for simple bug queries (the type that you click to from a link in an email).

[Bryce Nesbitt]

The next step is "explain select", e.g.:

mysql> explain select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (7)) and (b.project_id = '2') group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 0, 80;
+----+-------------+-----------------------+--------+----------------------------+----------------------------+---------+------------------------------------------+------+---------------------------------+
| id | select_type | table                 | type   | possible_keys              | key                        | key_len | ref                                      | rows | Extra                           |
+----+-------------+-----------------------+--------+----------------------------+----------------------------+---------+------------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | phpbt_database_server | system | PRIMARY                    | NULL                       | NULL    | NULL                                     |    0 | const row not found             |
|  1 | SIMPLE      | site                  | system | PRIMARY                    | NULL                       | NULL    | NULL                                     |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | status                | const  | PRIMARY                    | PRIMARY                    | 4       | const                                    |    1 |                                 |
|  1 | SIMPLE      | project               | const  | PRIMARY                    | PRIMARY                    | 4       | const                                    |    1 |                                 |
|  1 | SIMPLE      | b                     | ALL    | NULL                       | NULL                       | NULL    | NULL                                     | 2407 | Using where                     |
|  1 | SIMPLE      | version               | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.version_id                 |    1 |                                 |
|  1 | SIMPLE      | component             | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.component_id               |    1 |                                 |
|  1 | SIMPLE      | owner                 | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.assigned_to                |    1 |                                 |
|  1 | SIMPLE      | reporter              | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.created_by                 |    1 |                                 |
|  1 | SIMPLE      | lastmodifier          | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.last_modified_by           |    1 |                                 |
|  1 | SIMPLE      | comment               | ref    | phpbt_comment_bug_id_index | phpbt_comment_bug_id_index | 4       | bug_tracker.b.bug_id                     |    2 | Using index                     |
|  1 | SIMPLE      | severity              | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.severity_id                |    1 | Using where                     |
|  1 | SIMPLE      | priority              | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.priority                   |    1 | Using where                     |
|  1 | SIMPLE      | version2              | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.to_be_closed_in_version_id |    1 | Using index                     |
|  1 | SIMPLE      | attachment            | ALL    | NULL                       | NULL                       | NULL    | NULL                                     |  149 |                                 |
|  1 | SIMPLE      | vote                  | ref    | bug_id                     | bug_id                     | 4       | bug_tracker.b.bug_id                     |    1 | Using index                     |
|  1 | SIMPLE      | bookmark              | ALL    | NULL                       | NULL                       | NULL    | NULL                                     |    6 |                                 |
|  1 | SIMPLE      | resolution            | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.resolution_id              |    1 |                                 |
|  1 | SIMPLE      | os                    | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.os_id                      |    1 | Using where; Using index        |
|  1 | SIMPLE      | version3              | eq_ref | PRIMARY                    | PRIMARY                    | 4       | bug_tracker.b.closed_in_version_id       |    1 |                                 |
+----+-------------+-----------------------+--------+----------------------------+----------------------------+---------+------------------------------------------+------+---------------------------------+
20 rows in set (0.04 sec)

[Nobody/Anonymous]

Ok, I see.  There are two answers

1) You have a lot of attachments or bookmarks, which are cross joining resulting in large counts.  An index could solve this.

2) In query.php, it selects only the result columns you want, but it left joins the entire world.  If it only left joined what you need, it would be fast.

[Bryce Nesbitt]

> Why isn't bug_id within the tables attachment and comment set as default index?

It looks like it is in postgres.
It probably should be in mysql.

I encourage you to submit patches!

[albertino13]

the slow-query.log looks as follows after the following actions:

List all closed bugs (approx. 2700, disply 80 per page, select a specific page, select a bug)
Look for bookmarked bugs (open and closed).

The mysql conf file is posted at the end.

- - - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - -  - - - - - - -  - -- - - - - - - - - - - - - - - - - - - - - - - -
/usr/sbin/mysqld, Version: 5.0.38-Ubuntu_0ubuntu1.4-log (Ubuntu 7.04 distribution). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 080723 10:32:01
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 58
use bugtracker_test;
select count(distinct u.user_id) from phpbt_auth_user u;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 58  Rows_examined: 116
select distinct u.user_id, u.first_name, u.last_name, u.email, u.login, u.created_date, u.active from phpbt_auth_user u order by login asc LIMIT 0, 1000;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 4  Rows_examined: 13
select group_id, group_name from phpbt_auth_group where is_role = 0 order by group_name;
# Time: 080723 10:32:32
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 58
select count(distinct u.user_id) from phpbt_auth_user u;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 58  Rows_examined: 116
select distinct u.user_id, u.first_name, u.last_name, u.email, u.login, u.created_date, u.active from phpbt_auth_user u order by login asc LIMIT 0, 1000;
# Time: 080723 10:33:01
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 3
select project_id from phpbt_project_perm where user_id = 49;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3017
SELECT sum(CASE WHEN s.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) THEN 1 ELSE 0 END ), sum(CASE WHEN s.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) THEN 1 ELSE 0 END ) from phpbt_bug b left join phpbt_status s using(status_id) where assigned_to = 49;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3101
SELECT sum(CASE WHEN s.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) THEN 1 ELSE 0 END ), sum(CASE WHEN s.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) THEN 1 ELSE 0 END ) from phpbt_bug b left join phpbt_status s using(status_id) where created_by = 49;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 7
SELECT sum(CASE WHEN s.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) THEN 1 ELSE 0 END ), sum(CASE WHEN s.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) THEN 1 ELSE 0 END ) from phpbt_bug b left join phpbt_status s using(status_id), phpbt_bookmark w where w.user_id=49 AND w.bug_id = b.bug_id;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 14  Rows_examined: 151
select * from phpbt_saved_query where user_id = '49';
# Time: 080723 10:33:05
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3313
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.created_by = 49;
# Time: 080723 10:33:06
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 1  Lock_time: 0  Rows_sent: 43  Rows_examined: 682384
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.created_by = 49  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:33:13
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3304
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.created_by = 49;
# Time: 080723 10:33:14
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 1  Lock_time: 0  Rows_sent: 41  Rows_examined: 729041
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.created_by = 49  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:33:19
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3017
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and assigned_to = 49;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 3017
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and assigned_to = 49  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:33:20
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3017
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and assigned_to = 49;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 3017
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and assigned_to = 49  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:33:22
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 7
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.bug_id = bookmark.bug_id AND bookmark.user_id = 49;
# Time: 080723 10:33:41
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 19  Lock_time: 0  Rows_sent: 1  Rows_examined: 17675
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.bug_id = bookmark.bug_id AND bookmark.user_id = 49  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:33:49
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 6
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.bug_id = bookmark.bug_id AND bookmark.user_id = 49;
# Time: 080723 10:34:09
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 20  Lock_time: 0  Rows_sent: 0  Rows_examined: 81
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.bug_id = bookmark.bug_id AND bookmark.user_id = 49  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:35:00
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 21826
select count(*) from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id where (b.status_id in (7)) and (b.project_id = '2');
# Time: 080723 10:36:03
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 63  Lock_time: 0  Rows_sent: 80  Rows_examined: 52528734
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (7)) and (b.project_id = '2')  group by b.bug_id order by b.bug_id asc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:38:25
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 62  Lock_time: 0  Rows_sent: 80  Rows_examined: 52528734
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (7)) and (b.project_id = '2')  group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:54:21
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 63  Lock_time: 0  Rows_sent: 80  Rows_examined: 52530414
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (7)) and (b.project_id = '2')  group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 1680, 80;
# Time: 080723 10:54:26
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 6
select b.*, reporter.login as reporter, owner.login as owner, status_name, resolution_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution r on b.resolution_id = r.resolution_id, phpbt_severity sv, phpbt_status st, phpbt_site site, phpbt_priority prio where b.bug_id = 1022 and b.project_id not in (0) and b.site_id = site.site_id and b.severity_id = sv.severity_id and b.status_id = st.status_id and b.priority = prio.priority_id;
# Time: 080723 10:55:29
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 63  Lock_time: 0  Rows_sent: 2  Rows_examined: 52545476
select b.bug_id, reporter.login as reporter, owner.login as owner, count(distinct comment.comment_id) as comments, count(distinct attachment.attachment_id) as attachments, count(distinct vote.user_id) as votes from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id    left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_priority priority, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_site site where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (7)) and (b.project_id = '2') and b.site_id = site.site_id and b.bug_id <> 1022 group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 11, 2;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 3
select user_id from phpbt_project_perm where user_id = 49 and project_id = 2;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 151
select b.bug_id, s.bug_open from phpbt_bug_dependency d, phpbt_bug b, phpbt_status s where d.depends_on = 1022 and d.bug_id = b.bug_id and b.status_id = s.status_id;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 5
select count(*) from phpbt_bookmark where bug_id = 1022 and user_id = 49;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 1853
select * from phpbt_attachment where bug_id = 1022;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 2  Rows_examined: 12024
select comment_id,comment_text, c.created_date, login from phpbt_comment c, phpbt_auth_user where bug_id = 1022 and c.created_by = user_id order by c.created_date;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 2  Rows_examined: 482
SELECT
            s.server_name,
            s.id_server,
            bs.id_bug,
            bs.exist,
            bs.verified
        FROM
            phpbt_server s
            left join phpbt_server_bugcondition bs on s.id_server=bs.id_server
            AND bs.id_bug = 1022        
        ORDER BY
            s.id_server;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 57  Rows_examined: 115
select u.user_id, login from phpbt_auth_user u where u.active > 0  order by login;
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 0  Lock_time: 0  Rows_sent: 57  Rows_examined: 3175
select DISTINCT u.user_id, login from phpbt_auth_user u, phpbt_user_group ug, phpbt_group_perm gp, phpbt_auth_perm p where u.active > 0 and u.user_id = ug.user_id and ug.group_id = gp.group_id and gp.perm_id = p.perm_id and p.perm_name = 'Assignable' order by login;
# Time: 080723 10:55:36
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 1  Lock_time: 0  Rows_sent: 43  Rows_examined: 682384
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.created_by = 49  group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:55:39
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 1  Lock_time: 0  Rows_sent: 41  Rows_examined: 729041
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id not in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.created_by = 49  group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 0, 80;
# Time: 080723 10:56:01
# User@Host: root[root] @ NFTESTLINUX.nationalfonds.gv.at [192.168.2.11]
# Query_time: 20  Lock_time: 0  Rows_sent: 1  Rows_examined: 17675
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, severity.severity_name, priority.priority_name, status.status_name, resolution_name, version3.version_name as closed_in_version_name, phpbt_database_server.database_name, site.site_name, reporter.login as reporter, owner.login as owner, lastmodifier.login as lastmodifier, b.last_modified_date, project.project_name, version.version_name, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and b.status_id in (1, 2, 3, 4, 5, 6, 8, 9, 10, 12) and b.bug_id = bookmark.bug_id AND bookmark.user_id = 49  group by b.bug_id order by b.bug_id desc, b.bug_id asc LIMIT 0, 80;
- - - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - -  - - - - - - -  - -- - - - - - - - - - - - - - - - - - - - - - - - - -  -

The conf-file look as followd (clip)

# * Fine Tuning
#
key_buffer        = 32M
max_allowed_packet    = 16M
thread_stack        = 128K
thread_cache_size    = 16
max_connections        = 100
table_cache            = 32
thread_concurrency     = 100
#
# * Query Cache Configuration
#
query_cache_limit       = 10M
query_cache_size        = 64M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log        = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes

[albertino13]

Executing a query over all closed bugs takes about 60 seconds because the query build contains about 11 left joins by comparsion to version 1.01 which contains only 5-6 left joins.

[Bryce Nesbitt]

Ok, I see. There are two answers

1) You have a lot of attachments or bookmarks, which are cross joining resulting in large counts. An index could solve this.

2) In query.php, it selects only the result columns you want, but it left joins the entire world. If it only left joined what you need, it would be fast.

[Bryce Nesbitt]

Note that most of those left joins are to tiny tables with less than 10 rows.

[albertino13]

thank's a lot for the hint with 'explain sql ...'

Setting a correct primary keys and indexes on the tables attachments and comments as well as on os solved the problem and queries are now
very quick.

Why isn't bug_id within the tables attachment and comment set as default index?

[Bryce Nesbitt]

Fixed in cvs.  And, way faster than ever before, especially for "next" and "previous".