0005110: Slow SQL statement on my view page - MantisBT - Signup temporarily disabled due to spam

ID	Project	Category	View Status	Date Submitted	Last Update

0005110	mantisbt	sql	public	2005-01-12 17:18	2005-04-18 10:30

Reporter	cbystrom	Assigned To	thraxisp
Priority	normal	Severity	major	Reproducibility	always
Status	closed	Resolution	fixed
Product Version	0.19.2
Fixed in Version	1.0.0a1

Summary	0005110: Slow SQL statement on my view page
Description	The my_view page is creating a SQL statement that is causing some problems on our 2CPU server. We have over 7200 entries in our bug_table, and the my_view page (by way of the core/filter_api.php:filter_get_bug_rows() ) creates a SQL statement with over 6700 individual "mantis_bug_table.id = ??? OR" statements and then limits it to 10. This query takes 23 seconds to execute. Switching this to an SQL "IN" statement makes it take .58 seconds.I am attaching examples of SQL statment as generated by filter_get_bug_rows().
Tags	No tags attached.
Attached Files	mail-slow.zip (18,846 bytes) filter.patch (13,752 bytes) Index: view_all_bug_page.php =================================================================== RCS file: /cvsroot/mantisbt/mantisbt/view_all_bug_page.php,v retrieving revision 1.58 diff -u -r1.58 view_all_bug_page.php --- view_all_bug_page.php 13 Dec 2004 17:03:56 -0000 1.58 +++ view_all_bug_page.php 23 Jan 2005 18:15:37 -0000 @@ -29,14 +29,7 @@ $t_bug_count = null; $t_page_count = null; - if ( 1 == $f_page_number ) { - $sticky_rows = filter_get_bug_rows( $f_page_number, $t_per_page, $t_page_count, $t_bug_count, null, null, null, true ); - if ( $sticky_rows === false ) { - print_header_redirect( 'view_all_set.php?type=0' ); - } - } - - $rows = filter_get_bug_rows( $f_page_number, $t_per_page, $t_page_count, $t_bug_count ); + $rows = filter_get_bug_rows( $f_page_number, $t_per_page, $t_page_count, $t_bug_count, null, null, null, true ); if ( $rows === false ) { print_header_redirect( 'view_all_set.php?type=0' ); } Index: view_all_inc.php =================================================================== RCS file: /cvsroot/mantisbt/mantisbt/view_all_inc.php,v retrieving revision 1.150 diff -u -r1.150 view_all_inc.php --- view_all_inc.php 18 Dec 2004 01:44:26 -0000 1.150 +++ view_all_inc.php 23 Jan 2005 18:15:37 -0000 @@ -184,7 +184,9 @@ global $t_sort; global $t_dir; global $t_checkboxes_exist; + global $col_count; + $t_in_stickies = true; mark_time( 'begin loop' ); @@ -194,6 +196,18 @@ # prefix bug data with v_ extract( $p_rows[$i], EXTR_PREFIX_ALL, 'v' ); + + if ( ( 0 == $v_sticky ) && ( 0 == $i ) ) { + $t_in_stickies = false; + } + if ( ( 0 == $v_sticky ) && $t_in_stickies ) { +?> + <tr> + <td class="left" colspan="<?php echo $col_count; ?>" bgcolor="#999999"> </td> + </tr> +<?php + $t_in_stickies = false; + } $t_summary = string_attribute( $v_summary ); $t_last_updated = date( config_get( 'short_date_format' ), $v_last_updated ); @@ -368,15 +382,6 @@ ?> <?php - write_bug_rows($sticky_rows); - # -- ====================== end of STICKY BUG LIST ========================= -- - if ( 0 < sizeof($sticky_rows) ) { -?> - <tr> - <td class="left" colspan="<?php echo $col_count; ?>" bgcolor="#999999"> </td> - </tr> -<?php - } write_bug_rows($rows); # -- ====================== end of BUG LIST ========================= -- Index: core/filter_api.php =================================================================== RCS file: /cvsroot/mantisbt/mantisbt/core/filter_api.php,v retrieving revision 1.75 diff -u -r1.75 filter_api.php --- core/filter_api.php 15 Jan 2005 23:19:11 -0000 1.75 +++ core/filter_api.php 23 Jan 2005 18:15:38 -0000 @@ -103,15 +103,11 @@ if ( 0 == count( $t_projects ) ) { return array(); # no accessible projects, return an empty array + } else if ( 1 == count( $t_projects ) ) { + $t_project = $t_projects[0]; + array_push( $t_where_clauses, "( $t_bug_table.project_id=$t_project )" ); } else { - $t_clauses = array(); - - #@@@ use project_id IN (1,2,3,4) syntax if we can - for ( $i=0 ; $i < count( $t_projects ) ; $i++) { - array_push( $t_clauses, "($t_bug_table.project_id='$t_projects[$i]')" ); - } - - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + array_push( $t_where_clauses, "( $t_bug_table.project_id in (". implode( ', ', $t_projects ) . ") )" ); } } } else { @@ -147,20 +143,24 @@ foreach( $t_filter['reporter_id'] as $t_filter_member ) { if ( '[none]' == $t_filter_member ) { - array_push( $t_clauses, "($t_bug_table.reporter_id=0)" ); + array_push( $t_clauses, "0" ); } else { $c_reporter_id = db_prepare_int( $t_filter_member ); if ( META_FILTER_MYSELF == $c_reporter_id ) { if ( access_has_project_level( config_get( 'report_bug_threshold' ), $t_project_id, $t_user_id ) ) { $c_reporter_id = $c_user_id; - array_push( $t_clauses, "($t_bug_table.reporter_id='$c_reporter_id')" ); + array_push( $t_clauses, $c_reporter_id ); } } else { - array_push( $t_clauses, "($t_bug_table.reporter_id='$c_reporter_id')" ); + array_push( $t_clauses, $c_reporter_id ); } } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.reporter_id in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.reporter_id=$t_clauses[0] )" ); + } } # limit reporter @@ -185,20 +185,24 @@ foreach( $t_filter['handler_id'] as $t_filter_member ) { if ( '[none]' == $t_filter_member ) { - array_push( $t_clauses, "$t_bug_table.handler_id=0" ); + array_push( $t_clauses, 0 ); } else { $c_handler_id = db_prepare_int( $t_filter_member ); if ( META_FILTER_MYSELF == $c_handler_id ) { if ( access_has_project_level( config_get( 'handle_bug_threshold' ), $t_project_id, $t_user_id ) ) { $c_handler_id = $c_user_id; - array_push( $t_clauses, "($t_bug_table.handler_id='$c_handler_id')" ); + array_push( $t_clauses, $c_handler_id ); } } else { - array_push( $t_clauses, "($t_bug_table.handler_id='$c_handler_id')" ); + array_push( $t_clauses, $c_handler_id ); } } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.handler_id in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.handler_id=$t_clauses[0] )" ); + } } # category @@ -218,13 +222,17 @@ foreach( $t_filter['show_category'] as $t_filter_member ) { $t_filter_member = stripslashes( $t_filter_member ); if ( '[none]' == $t_filter_member ) { - array_push( $t_clauses, "$t_bug_table.category=''" ); + array_push( $t_clauses, "''" ); } else { $c_show_category = db_prepare_string( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.category='$c_show_category')" ); + array_push( $t_clauses, "'$c_show_category'" ); } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.category in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.category=$t_clauses[0] )" ); + } } # severity @@ -242,9 +250,13 @@ foreach( $t_filter['show_severity'] as $t_filter_member ) { $c_show_severity = db_prepare_int( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.severity='$c_show_severity')" ); + array_push( $t_clauses, $c_show_severity ); + } + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.severity in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.severity=$t_clauses[0] )" ); } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); } # show / hide status @@ -295,9 +307,13 @@ foreach( $t_desired_statuses as $t_filter_member ) { $c_show_status = db_prepare_int( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.status='$c_show_status')" ); + array_push( $t_clauses, $c_show_status ); + } + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.status in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.status=$t_clauses[0] )" ); } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); } # resolution @@ -315,9 +331,13 @@ foreach( $t_filter['show_resolution'] as $t_filter_member ) { $c_show_resolution = db_prepare_int( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.resolution='$c_show_resolution')" ); + array_push( $t_clauses, $c_show_resolution ); + } + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.resolution in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.resolution=$t_clauses[0] )" ); } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); } # priority @@ -335,9 +355,13 @@ foreach( $t_filter['show_priority'] as $t_filter_member ) { $c_show_priority = db_prepare_int( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.priority='$c_show_priority')" ); + array_push( $t_clauses, $c_show_priority ); } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.priority in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.priority=$t_clauses[0] )" ); + } } @@ -357,13 +381,17 @@ foreach( $t_filter['show_build'] as $t_filter_member ) { $t_filter_member = stripslashes( $t_filter_member ); if ( '[none]' == $t_filter_member ) { - array_push( $t_clauses, "($t_bug_table.build='')" ); + array_push( $t_clauses, "''" ); } else { $c_show_build = db_prepare_string( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.build='$c_show_build')" ); + array_push( $t_clauses, "'$c_show_build'" ); } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.build in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.build=$t_clauses[0] )" ); + } } # product version @@ -382,13 +410,17 @@ foreach( $t_filter['show_version'] as $t_filter_member ) { $t_filter_member = stripslashes( $t_filter_member ); if ( '[none]' == $t_filter_member ) { - array_push( $t_clauses, "($t_bug_table.version='')" ); + array_push( $t_clauses, "''" ); } else { $c_show_version = db_prepare_string( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.version='$c_show_version')" ); + array_push( $t_clauses, "'$c_show_version'" ); } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.version in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.version=$t_clauses[0] )" ); + } } # date filter @@ -423,13 +455,17 @@ foreach( $t_filter['fixed_in_version'] as $t_filter_member ) { $t_filter_member = stripslashes( $t_filter_member ); if ( '[none]' == $t_filter_member ) { - array_push( $t_clauses, "($t_bug_table.fixed_in_version='')" ); + array_push( $t_clauses, "''" ); } else { $c_fixed_in_version = db_prepare_string( $t_filter_member ); - array_push( $t_clauses, "($t_bug_table.fixed_in_version='$c_fixed_in_version')" ); + array_push( $t_clauses, "'$c_fixed_in_version'" ); } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_bug_table.fixed_in_version in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_bug_table.fixed_in_version=$t_clauses[0] )" ); + } } # users monitoring a bug @@ -453,13 +489,17 @@ if ( META_FILTER_MYSELF == $c_user_monitor ) { if ( access_has_project_level( config_get( 'monitor_bug_threshold' ), $t_project_id, $t_user_id ) ) { $c_user_monitor = $c_user_id; - array_push( $t_clauses, "($t_table_name.user_id='$c_user_monitor')" ); + array_push( $t_clauses, $c_user_id ); } } else { - array_push( $t_clauses, "($t_table_name.user_id='$c_user_monitor')" ); + array_push( $t_clauses, $c_user_monitor ); } } - array_push( $t_where_clauses, '('. implode( ' OR ', $t_clauses ) .')' ); + if ( 1 < count( $t_clauses ) ) { + array_push( $t_where_clauses, "( $t_table_name.user_id in (". implode( ', ', $t_clauses ) .") )" ); + } else { + array_push( $t_where_clauses, "( $t_table_name.user_id=$t_clauses[0] )" ); + } } # custom field filters @@ -558,16 +598,11 @@ } else { $t_where = ''; } - - if ( null === $p_show_sticky ) { - $t_where .= " AND $t_bug_table.sticky = 0"; - } - else { - $t_where .= " AND $t_bug_table.sticky = 1"; - } - - if ( false === $t_filter['sticky_issues'] && true === $p_show_sticky ) { - $t_where .= " AND 1 = 0"; + + if ( ( 'on' == $t_filter['sticky_issues'] ) && ( NULL !== $p_show_sticky ) ) { + $t_sticky_order = " sticky DESC, "; + } else { + $t_sticky_order = ""; } # Possibly do two passes. First time, grab the IDs of issues that match the filters. Second time, grab the IDs of issues that @@ -602,7 +637,7 @@ $t_id_array = array_unique( $t_id_array ); if ( count( $t_id_array ) > 0 ) { - $t_where = "WHERE $t_bug_table.id = " . implode( " OR $t_bug_table.id = ", $t_id_array ); + $t_where = "WHERE $t_bug_table.id in (" . implode( ", ", $t_id_array ) . ")"; } else { $t_where = "WHERE 1 != 1"; } @@ -672,7 +707,7 @@ $c_dir = 'ASC'; } - $query2 .= " ORDER BY $c_sort $c_dir"; + $query2 .= " ORDER BY $t_sticky_order $c_sort $c_dir"; if ( $c_sort != 'last_updated' ) { $query2 .= ', last_updated DESC, date_submitted DESC'; } filter.patch (13,752 bytes) filter.zip (18,694 bytes)

guideweb 2005-01-12 18:11 reporter ~0008982	filter_api.php line 604

thraxisp 2005-01-23 02:24 reporter ~0009056	I've provided a patch and replacement files for you to test. Please post a confirmation that this helps your problem before I commit the update. Files updated: view_all_bug_page.php, view_all_inc.php, core/filter_api.php

thraxisp 2005-01-23 02:27 reporter ~0009057	Reminder sent to: bpfennig I found a problem with the stickies while adding this patch. On the second (or greater) page of view_all_bugs_page, there is a failure. I also removed the double queries for stickies. Can you review this?

bpfennig 2005-01-23 04:02 reporter ~0009058	I couldn't reproduce your failure - but if I select "All Projects" I got a database error on My View and View Issues pages.

thraxisp 2005-01-24 05:18 reporter ~0009060	New patch and files uploaded.

cbystrom 2005-01-28 12:25 reporter ~0009126	I used the replacement files and the page draws much faster. I can see in my mysql query log that the new query is using an IN and works much better. I also did some data clean up in our system making sure that there weren't any bugs without text table entries, user prefs without user records, etc. I don't know if this had any impact but that also improved the speed of the page. All in all, the patch seems to be much better then the previous code.

thraxisp 2005-01-28 21:31 reporter ~0009129	fix submitted to CVS. I measured about 50% improvement in search timing. I also removed the double queries for finding stickies.

View Issue Details

Activities