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

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, enhancement)

Description

[Max Kanat-Alexander]

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]

Attachment: Work In Progress

This works, but it breaks the flagtypes.name/anywords search in MySQL.

Comment 2

[Max Kanat-Alexander]

Attachment: 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.

Comment 3

[Max Kanat-Alexander]

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]

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]

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.

Comment 6

[Frédéric Buclin]

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]

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