Note: There are a few cases of duplicates in user autocompletion which are being worked on.

Search.pm: Improve the implementation and performance of substring and "words" searches

RESOLVED FIXED in Bugzilla 4.2

Status

()

Bugzilla
Query/Bug List
--
enhancement
RESOLVED FIXED
7 years ago
5 years ago

People

(Reporter: Max Kanat-Alexander, Assigned: Max Kanat-Alexander)

Tracking

(Blocks: 2 bugs)

3.7.2
Bugzilla 4.2
Dependency tree / graph
Bug Flags:
approval +

Details

Attachments

(1 attachment, 1 obsolete attachment)

v1
9.70 KB, patch
Max Kanat-Alexander
: review+
Details | Diff | Splinter Review
(Assignee)

Description

7 years ago
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.
(Assignee)

Comment 1

7 years ago
Created attachment 458033 [details] [diff] [review]
Work In Progress

This works, but it breaks the flagtypes.name/anywords search in MySQL.
Assignee: query-and-buglist → mkanat
Status: NEW → ASSIGNED
(Assignee)

Comment 2

7 years ago
Created attachment 458134 [details] [diff] [review]
v1

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 Search.pm generate slightly more readable SQL.
Attachment #458033 - Attachment is obsolete: true
Attachment #458134 - Flags: review+
(Assignee)

Updated

7 years ago
Flags: approval+
(Assignee)

Comment 3

7 years ago
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.
(Assignee)

Comment 4

7 years ago
The WORD_START and WORD_END constants may need to be different on Oracle and MS-SQL, depending on how their regex engines work.
(Assignee)

Comment 5

7 years ago
Note that MS-SQL and Oracle may still need fixes.

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/trunk/
modified Bugzilla/DB.pm
modified Bugzilla/Search.pm
modified xt/lib/Bugzilla/Test/Search/Constants.pm
Committed revision 7381.
Status: ASSIGNED → RESOLVED
Last Resolved: 7 years ago
Resolution: --- → FIXED

Comment 6

7 years ago
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.
(Assignee)

Comment 7

7 years ago
(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.

Updated

5 years ago
Blocks: 814361
You need to log in before you can comment on or make changes to this bug.