Last Comment Bug 579568 - Improve the implementation and performance of substring and "words" searches
: Improve the implementation and performance of substring and "words...
Product: Bugzilla
Classification: Server Software
Component: Query/Bug List (show other bugs)
: 3.7.2
: All All
: -- enhancement (vote)
: Bugzilla 4.2
Assigned To: Max Kanat-Alexander
: default-qa
Depends on:
Blocks: 157952 814361
  Show dependency treegraph
Reported: 2010-07-16 16:08 PDT by Max Kanat-Alexander
Modified: 2012-11-22 09:18 PST (History)
2 users (show)
mkanat: approval+
See Also:
QA Whiteboard:
Iteration: ---
Points: ---

Work In Progress (6.25 KB, patch)
2010-07-16 18:40 PDT, Max Kanat-Alexander
no flags Details | Diff | Splinter Review
v1 (9.70 KB, patch)
2010-07-17 17:48 PDT, Max Kanat-Alexander
mkanat: review+
Details | Diff | Splinter Review

Description Max Kanat-Alexander 2010-07-16 16:08:45 PDT
There are a lot of things that the words/substring searches do that are non-optimal, and their implementation is a little hard to read. I have some improvements that I know I can make to them to improve the performance of the "words" searches, and the architecture of both.

This will probably mitigate bug 157952, even if it doesn't solve it totally.
Comment 1 Max Kanat-Alexander 2010-07-16 18:40:43 PDT
Created attachment 458033 [details] [diff] [review]
Work In Progress

This works, but it breaks the search in MySQL.
Comment 2 Max Kanat-Alexander 2010-07-17 17:48:49 PDT
Created attachment 458134 [details] [diff] [review]

This improves the performance and implementation of the substring and words searches.

I changed the definition of a "word" to include numbers, so "blah" will not match "blah1" now, if used as an anywords/allwords/nowords search.

Also, for anything using build_subselect, I just return the subselect SQL instead of doing another query, now. This should be a bit faster, particularly in situations where doing fewer queries significantly impacts performance (like a MySQL server connected over a slightly laggy link to the webserver).

On some databases, the anywords/allwords/nowords searches will now work properly with Unicode--it depends on the Unicode support of the regular expression engine in the database in use, now.

I also made generate slightly more readable SQL.
Comment 3 Max Kanat-Alexander 2010-07-17 17:49:58 PDT
By the way, this causes some tests to pass on Pg that previously did not pass, but it *only* fixes them on Pg. Instead of implementing a whole new framework to deal with that slight testing difference on Pg, I'm just going to let the Pg xt tinderbox turn orange for now.
Comment 4 Max Kanat-Alexander 2010-07-17 17:50:34 PDT
The WORD_START and WORD_END constants may need to be different on Oracle and MS-SQL, depending on how their regex engines work.
Comment 5 Max Kanat-Alexander 2010-07-17 17:55:30 PDT
Note that MS-SQL and Oracle may still need fixes.

Committing to: bzr+ssh://
modified Bugzilla/
modified Bugzilla/
modified xt/lib/Bugzilla/Test/Search/
Committed revision 7381.
Comment 6 Frédéric Buclin 2010-07-18 02:18:30 PDT
Do you have some metrics which show the perf improvement? This is to see if we are talking about 2x faster, 50% faster or 5% faster; just to get an idea.
Comment 7 Max Kanat-Alexander 2010-07-18 03:28:24 PDT
(In reply to comment #6)
> Do you have some metrics which show the perf improvement? This is to see if we
> are talking about 2x faster, 50% faster or 5% faster; just to get an idea.

  I'd like to, but I don't. I don't have a test bed that can tell me that effectively enough. It's still too slow to reasonably use on bmo without any other criteria. However, it's reasonably fast now if you specify some additional criteria, like limiting it to a product.

Note You need to log in before you can comment on or make changes to this bug.