0002921: search gets slow as the number of bugs/bugnotes grows, i re-write the simple text search code

(0003905)
reporter (reporter)
2003-03-03 15:31

i am the one opened this case.
The problem is obvious if you have many bugs with hundreds of bugnotes each. which is our case since the bugs are created automatically by our monitoring applications

(0003921)
jfitzell (reporter)
2003-03-05 03:02

Could you describe the changes you made? This code will likely no longer work directly in the CVS codebase so someone will need to apply the general idea of the change. What was your solution to the problem?

(0003930)
reporter (reporter)
2003-03-05 06:29

the idea is to break one JOIN that involves 4 tables into 2 JOINs that each involves 2 tables. This uses much less resource when many bugs have lots of bugnotes. I believe use the new mysql command "EXPLAIN SELECT" in mysql v4 should help explain why.

I added comments to the code below.

--start--
#search in the bugnotes for $f_search
$query3_1 = "SELECT DISTINCT $g_mantis_bugnote_table.bug_id FROM $g_mantis_bugnote_table INNER JOIN $g_mantis_bugnote_text_table
ON $g_mantis_bugnote_table.bugnote_text_id = $g_mantis_bugnote_text_table.id
WHERE
$g_mantis_bugnote_text_table.note LIKE '%".addslashes($f_search)."%'";

#search in the bug fields for $f_search
$query3_2 = "SELECT DISTINCT $g_mantis_bug_table.id FROM $g_mantis_bug_table INNER JOIN $g_mantis_bug_text_table
ON $g_mantis_bug_table.bug_text_id = $g_mantis_bug_text_table.id WHERE ($g_mantis_bug_text_table.description LIKE '%".addslashes($f_search)."%')
OR ($g_mantis_bug_text_table.steps_to_reproduce LIKE '%".addslashes($f_search)."%')
OR ($g_mantis_bug_text_table.additional_information LIKE '%".addslashes($f_search)."%')
OR ($g_mantis_bug_table.summary LIKE '%".addslashes($f_search)."%')";

$result3_1 = db_query( $query3_1 );
$result3_2 = db_query( $query3_2 );
$t_bug_ids = "";

#concatenate bug ids, delimited by ","
while($row = db_fetch_array($result3_1)) {
    if($t_bug_ids){
        $t_bug_ids .= ",".$row[0];
    }else{
        $t_bug_ids .= $row[0];
    }
}
#now $t_bug_ids is something like "1,2,4,6,.."

#concatenate bug ids, delimited by ","
while($row = db_fetch_array($result3_2)) {
    if($t_bug_ids){
        $t_bug_ids .= ",".$row[0];
    }else{
        $t_bug_ids .= $row[0];
    }
}
#now $t_bug_ids is something like "1,2,4,6,...3,2,4,14..."

if($t_bug_ids){
    $t_bug_ids = "$g_mantis_bug_table.id IN ($t_bug_ids)";
}else{
    #none found
    $t_bug_ids = "$g_mantis_bug_table.id IN (NULL)";
}

$t_columns_clause = " $g_mantis_bug_table.*";
$t_where_clause .= " AND ($t_bug_ids)";
$t_from_clause = " FROM $g_mantis_bug_table";
--end--

(0003963)
int2str (reporter)
2003-03-12 00:07

It seems to me like the joins are of no real benefit anyway. Wouldn't it be even quicker to do 3 separate selects on each individual table (bugnote_table, bug_table, bug_text_table). In addition, I also thing we should weed out duplicate bug IDs manually instead of using the SQL "IN" function.

I would test this and benchmark it, but unfortunately my Mantis installation is tiny with not many bugs or bugnotes, so benchmarks wouldn't be all to conclusive.

(0003967)
jfitzell (reporter)
2003-03-12 08:07

My understanding is that it's the IN clause isn't removing duplicates, it's making sure we only select data for the bugs we're interested in. I hate that we're sending a huge string as the query but I'm not sure there's a better solution here is there?

We need to get the data on the correct bugs, sorted by the correct field and in a defined order so when we go page by page it works right.

I've been doing the sorting in PHP code in a lot of cases but since some of this includes this could be problematic. Actually I guess we could pull out the date as a number of seconds since the epoch and could sort on that.

Anyway, I'm blathering... I haven't looked closely at this code yet.

(0004282)
AJCartmell (reporter)
2003-05-01 15:13

I've managed to get the new BOOLEAN text search working using MySQL 4 - seems quick enough with several thousand bugs and is MUCH more useful for finding stuff. Of course the stuff above about splitting the search into seperate queries may still speed things up again.

Replace:

array_push( $t_where_clauses,
"((summary LIKE '%$c_search%')
OR ($t_bug_text_table.description LIKE '%$c_search%')
OR ($t_bug_text_table.steps_to_reproduce LIKE '%$c_search%')
OR ($t_bug_text_table.additional_information LIKE '%$c_search%')
OR ($t_bug_table.id LIKE '%$c_search%')
OR ($t_bugnote_text_table.note LIKE '%$c_search%'))" );

With:

array_push($t_where_clauses, "MATCH (summary, $t_bug_text_table.description, steps_to_reproduce, additional_information, $t_bugnote_text_table.note) AGAINST ('".addslashes($c_search)."' IN BOOLEAN MODE)");

(I don't unclude the bug id in the search as we use a per-project numbering system with a project-specific prefix. The jump facility goes to a given bug now anyway.)

I would be interested if anyone who was suffering with slow searches could try this and see how fast it is?

(0004290)
hucker (reporter)
2003-05-02 18:19

I have put in a not about this some time ago in somewhere, but wouldn't it make sense to take all of 'words' in the bug notes, descriptions and other text fields and directly index them in a table? You can get very fancy with this...

That would enable something that I've been very interested in, the ability to do 'real' searches. I'd like to be able to type in the query text -> 'speed motor noise' and have it pull up the bugs that have each of these terms in them (as opposed to the bugs that have the string 'speed motor noise'. This gets really important as the size of the database increases.

(0004723)
bzheng (reporter)
2003-11-10 17:51

anyway, here is my code again, in case someone also found the search un-acceptably slow, now should be in filter_api.php.
for Simple Text Search.
starting from line 143, replacing everything in the curly brakets that follows.
The point is, again, to avoid the left joins.
{
            $c_search = db_prepare_string( $t_filter['search'] );

            $query3_1 = "SELECT DISTINCT $t_bugnote_table.bug_id FROM $t_bugnote_table INNER JOIN $t_bugnote_text_table
                        ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id
                        WHERE
                        $t_bugnote_text_table.note LIKE '%".addslashes($c_search)."%'";
            $query3_2 = "SELECT DISTINCT $t_bug_table.id FROM $t_bug_table INNER JOIN $t_bug_text_table
                        ON $t_bug_table.bug_text_id = $t_bug_text_table.id WHERE ($t_bug_text_table.description LIKE '%".addslashes($c_search)."%')
                         OR ($t_bug_text_table.steps_to_reproduce LIKE '%".addslashes($c_search)."%')
                         OR ($t_bug_text_table.additional_information LIKE '%".addslashes($c_search)."%')
                         OR ($t_bug_table.summary LIKE '%".addslashes($c_search)."%')";

            $result3_1 = db_query( $query3_1 );
            $result3_2 = db_query( $query3_2 );
            $t_bug_ids = "";
            while($row = db_fetch_array($result3_1)) {
                if($t_bug_ids){
                    $t_bug_ids .= ",".$row[0];
                }else{
                    $t_bug_ids .= $row[0];
                }
            }
            while($row = db_fetch_array($result3_2)) {
                if($t_bug_ids){
                    $t_bug_ids .= ",".$row[0];
                }else{
                    $t_bug_ids .= $row[0];
                }
            }
            if($t_bug_ids){
                $t_bug_ids = $t_bug_ids;
            }else{
                $t_bug_ids = "NULL";
            }

            array_push( $t_where_clauses,
                            "(($t_bug_table.id LIKE '%$c_search%')
                             OR $t_bug_table.id IN ($t_bug_ids))" );
        }

(0004724)
AJCartmell (reporter)
2003-11-10 18:13

hucker, I think what you're describing is the MySQL full-text search. It creates a word index and allows various boolean searches, like Google. We now have full-text enabled for words of 2 characters and up, and it works extremely well.

There are draw-backs to the full-text search as you can't search for "non-word" strings such as ASX-45-thing, so we have a toggle for "Exact match" versus "Words (boolean)" options when searching.

(0005429)
tgrignon (reporter)
2004-04-23 16:56

Thanks BZheng! Works like a charm.

(0006323)
grangeway (developer)
2004-07-24 09:10

Do the 0.19 alpha's still have the issue discussed here?

(0007161)
grangeway (developer)
2004-08-25 17:20

i've added a related issue to 0004345 which may be similar to this.

In bug 0004348, there is a patch to support displaying query times for each db_query command. If anyone is having any slow queries, if you could apply that patch and attach a copy of your query list here ?

(0017725)
grangeway (developer)
2008-05-01 18:27

Thank you for taking the time to report a problem with mantis.

A number of optimisations have been applied to the filter code in 1.0, 1.1 and even the upcoming 1.2. Could you let us know with a new bug report if there are issues unaddressed by these changes.

Since this problem report was originally made, a number of releases have occurred.
It appears that this issue has either been fixed, or may not be a relevant report for the current release.

Unfortunately you are not using the latest version and the problem might already be fixed. Please download the latest release from http://www.mantisbt.org/download.php [^]

If you are able to reproduce this bug in the current release, or have some more information on how this feature could be improved in the current release. Please either change the mantis version on this bug report
to the version you tested and change the status back to "Open", or open a new issue report wit more information.

Again, thank you for your continued support and report.
Paul

Issue History
Date Modified	Username	Field	Change
2003-03-03 15:31	reporter	Note Added: 0003905
2003-03-05 03:02	jfitzell	Note Added: 0003921
2003-03-05 06:29	reporter	Note Added: 0003930
2003-03-12 00:07	int2str	Note Added: 0003963
2003-03-12 00:09	int2str	Issue Monitored: int2str
2003-03-12 01:36	moturi12	Issue Monitored: moturi12
2003-03-12 08:07	jfitzell	Note Added: 0003967
2003-05-01 15:13	AJCartmell	Note Added: 0004282
2003-05-02 18:19	hucker	Note Added: 0004290
2003-11-10 17:51	bzheng	Note Added: 0004723
2003-11-10 18:13	AJCartmell	Note Added: 0004724
2004-04-23 16:56	tgrignon	Note Added: 0005429
2004-04-23 16:56	tgrignon	Issue Monitored: tgrignon
2004-07-24 09:10	grangeway	Note Added: 0006323
2004-08-25 17:18	grangeway	Relationship added	related to 0004345
2004-08-25 17:20	grangeway	Note Added: 0007161
2004-08-25 17:21	grangeway	Reporter	reporter => 111
2004-08-25 17:21	grangeway	Status	new => feedback
2004-11-10 03:16	polzin	Issue Monitored: polzin
2006-09-15 02:51	roehlerf	Issue Monitored: roehlerf
2007-05-31 05:14	roehlerf	Issue End Monitor: roehlerf
2008-05-01 18:27	grangeway	Status	feedback => resolved
2008-05-01 18:27	grangeway	Resolution	open => unable to reproduce
2008-05-01 18:27	grangeway	Assigned To	=> grangeway
2008-05-01 18:27	grangeway	Note Added: 0017725
2008-08-12 09:36	grangeway	Status	resolved => closed

Notes
(0003905) reporter (reporter) 2003-03-03 15:31	i am the one opened this case. The problem is obvious if you have many bugs with hundreds of bugnotes each. which is our case since the bugs are created automatically by our monitoring applications

(0003921) jfitzell (reporter) 2003-03-05 03:02	Could you describe the changes you made? This code will likely no longer work directly in the CVS codebase so someone will need to apply the general idea of the change. What was your solution to the problem?

(0003930) reporter (reporter) 2003-03-05 06:29	the idea is to break one JOIN that involves 4 tables into 2 JOINs that each involves 2 tables. This uses much less resource when many bugs have lots of bugnotes. I believe use the new mysql command "EXPLAIN SELECT" in mysql v4 should help explain why. I added comments to the code below. --start-- #search in the bugnotes for $f_search $query3_1 = "SELECT DISTINCT $g_mantis_bugnote_table.bug_id FROM $g_mantis_bugnote_table INNER JOIN $g_mantis_bugnote_text_table ON $g_mantis_bugnote_table.bugnote_text_id = $g_mantis_bugnote_text_table.id WHERE $g_mantis_bugnote_text_table.note LIKE '%".addslashes($f_search)."%'"; #search in the bug fields for $f_search $query3_2 = "SELECT DISTINCT $g_mantis_bug_table.id FROM $g_mantis_bug_table INNER JOIN $g_mantis_bug_text_table ON $g_mantis_bug_table.bug_text_id = $g_mantis_bug_text_table.id WHERE ($g_mantis_bug_text_table.description LIKE '%".addslashes($f_search)."%') OR ($g_mantis_bug_text_table.steps_to_reproduce LIKE '%".addslashes($f_search)."%') OR ($g_mantis_bug_text_table.additional_information LIKE '%".addslashes($f_search)."%') OR ($g_mantis_bug_table.summary LIKE '%".addslashes($f_search)."%')"; $result3_1 = db_query( $query3_1 ); $result3_2 = db_query( $query3_2 ); $t_bug_ids = ""; #concatenate bug ids, delimited by "," while($row = db_fetch_array($result3_1)) { if($t_bug_ids){ $t_bug_ids .= ",".$row[0]; }else{ $t_bug_ids .= $row[0]; } } #now $t_bug_ids is something like "1,2,4,6,.." #concatenate bug ids, delimited by "," while($row = db_fetch_array($result3_2)) { if($t_bug_ids){ $t_bug_ids .= ",".$row[0]; }else{ $t_bug_ids .= $row[0]; } } #now $t_bug_ids is something like "1,2,4,6,...3,2,4,14..." if($t_bug_ids){ $t_bug_ids = "$g_mantis_bug_table.id IN ($t_bug_ids)"; }else{ #none found $t_bug_ids = "$g_mantis_bug_table.id IN (NULL)"; } $t_columns_clause = " $g_mantis_bug_table.*"; $t_where_clause .= " AND ($t_bug_ids)"; $t_from_clause = " FROM $g_mantis_bug_table"; --end--

(0003963) int2str (reporter) 2003-03-12 00:07	It seems to me like the joins are of no real benefit anyway. Wouldn't it be even quicker to do 3 separate selects on each individual table (bugnote_table, bug_table, bug_text_table). In addition, I also thing we should weed out duplicate bug IDs manually instead of using the SQL "IN" function. I would test this and benchmark it, but unfortunately my Mantis installation is tiny with not many bugs or bugnotes, so benchmarks wouldn't be all to conclusive.

(0003967) jfitzell (reporter) 2003-03-12 08:07	My understanding is that it's the IN clause isn't removing duplicates, it's making sure we only select data for the bugs we're interested in. I hate that we're sending a huge string as the query but I'm not sure there's a better solution here is there? We need to get the data on the correct bugs, sorted by the correct field and in a defined order so when we go page by page it works right. I've been doing the sorting in PHP code in a lot of cases but since some of this includes this could be problematic. Actually I guess we could pull out the date as a number of seconds since the epoch and could sort on that. Anyway, I'm blathering... I haven't looked closely at this code yet.

(0004282) AJCartmell (reporter) 2003-05-01 15:13	I've managed to get the new BOOLEAN text search working using MySQL 4 - seems quick enough with several thousand bugs and is MUCH more useful for finding stuff. Of course the stuff above about splitting the search into seperate queries may still speed things up again. Replace: array_push( $t_where_clauses, "((summary LIKE '%$c_search%') OR ($t_bug_text_table.description LIKE '%$c_search%') OR ($t_bug_text_table.steps_to_reproduce LIKE '%$c_search%') OR ($t_bug_text_table.additional_information LIKE '%$c_search%') OR ($t_bug_table.id LIKE '%$c_search%') OR ($t_bugnote_text_table.note LIKE '%$c_search%'))" ); With: array_push($t_where_clauses, "MATCH (summary, $t_bug_text_table.description, steps_to_reproduce, additional_information, $t_bugnote_text_table.note) AGAINST ('".addslashes($c_search)."' IN BOOLEAN MODE)"); (I don't unclude the bug id in the search as we use a per-project numbering system with a project-specific prefix. The jump facility goes to a given bug now anyway.) I would be interested if anyone who was suffering with slow searches could try this and see how fast it is?

(0004290) hucker (reporter) 2003-05-02 18:19	I have put in a not about this some time ago in somewhere, but wouldn't it make sense to take all of 'words' in the bug notes, descriptions and other text fields and directly index them in a table? You can get very fancy with this... That would enable something that I've been very interested in, the ability to do 'real' searches. I'd like to be able to type in the query text -> 'speed motor noise' and have it pull up the bugs that have each of these terms in them (as opposed to the bugs that have the string 'speed motor noise'. This gets really important as the size of the database increases.

(0004723) bzheng (reporter) 2003-11-10 17:51	anyway, here is my code again, in case someone also found the search un-acceptably slow, now should be in filter_api.php. for Simple Text Search. starting from line 143, replacing everything in the curly brakets that follows. The point is, again, to avoid the left joins. { $c_search = db_prepare_string( $t_filter['search'] ); $query3_1 = "SELECT DISTINCT $t_bugnote_table.bug_id FROM $t_bugnote_table INNER JOIN $t_bugnote_text_table ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id WHERE $t_bugnote_text_table.note LIKE '%".addslashes($c_search)."%'"; $query3_2 = "SELECT DISTINCT $t_bug_table.id FROM $t_bug_table INNER JOIN $t_bug_text_table ON $t_bug_table.bug_text_id = $t_bug_text_table.id WHERE ($t_bug_text_table.description LIKE '%".addslashes($c_search)."%') OR ($t_bug_text_table.steps_to_reproduce LIKE '%".addslashes($c_search)."%') OR ($t_bug_text_table.additional_information LIKE '%".addslashes($c_search)."%') OR ($t_bug_table.summary LIKE '%".addslashes($c_search)."%')"; $result3_1 = db_query( $query3_1 ); $result3_2 = db_query( $query3_2 ); $t_bug_ids = ""; while($row = db_fetch_array($result3_1)) { if($t_bug_ids){ $t_bug_ids .= ",".$row[0]; }else{ $t_bug_ids .= $row[0]; } } while($row = db_fetch_array($result3_2)) { if($t_bug_ids){ $t_bug_ids .= ",".$row[0]; }else{ $t_bug_ids .= $row[0]; } } if($t_bug_ids){ $t_bug_ids = $t_bug_ids; }else{ $t_bug_ids = "NULL"; } array_push( $t_where_clauses, "(($t_bug_table.id LIKE '%$c_search%') OR $t_bug_table.id IN ($t_bug_ids))" ); }

(0004724) AJCartmell (reporter) 2003-11-10 18:13	hucker, I think what you're describing is the MySQL full-text search. It creates a word index and allows various boolean searches, like Google. We now have full-text enabled for words of 2 characters and up, and it works extremely well. There are draw-backs to the full-text search as you can't search for "non-word" strings such as ASX-45-thing, so we have a toggle for "Exact match" versus "Words (boolean)" options when searching.

(0005429) tgrignon (reporter) 2004-04-23 16:56	Thanks BZheng! Works like a charm.

(0006323) grangeway (developer) 2004-07-24 09:10	Do the 0.19 alpha's still have the issue discussed here?

(0007161) grangeway (developer) 2004-08-25 17:20	i've added a related issue to 0004345 which may be similar to this. In bug 0004348, there is a patch to support displaying query times for each db_query command. If anyone is having any slow queries, if you could apply that patch and attach a copy of your query list here ?

(0017725) grangeway (developer) 2008-05-01 18:27	Thank you for taking the time to report a problem with mantis. A number of optimisations have been applied to the filter code in 1.0, 1.1 and even the upcoming 1.2. Could you let us know with a new bug report if there are issues unaddressed by these changes. Since this problem report was originally made, a number of releases have occurred. It appears that this issue has either been fixed, or may not be a relevant report for the current release. Unfortunately you are not using the latest version and the problem might already be fixed. Please download the latest release from http://www.mantisbt.org/download.php [^] If you are able to reproduce this bug in the current release, or have some more information on how this feature could be improved in the current release. Please either change the mantis version on this bug report to the version you tested and change the status back to "Open", or open a new issue report wit more information. Again, thank you for your continued support and report. Paul