Improve summary search speed on large installs.
Review Request #6494 — Created Oct. 23, 2014 and submitted
Search for summaries in the quick search is very slow, for a couple of
reasons. We didn't have an index on the summary, but even if we did,
we were looking for text in the middle of the summary field, which
indexes can't help with.This could cause real problems on large installs, slowing down the
database making it nearly impossible to find what you're looking for.
Continuing to type would trigger further expensive queries, eventually
resulting in other queries stalling on the server.To fix this, we now have an index, and we now search only the beginnings
of summaries. We also only start searching if we have 4 or more
characters. It limitation on where we search sucks, but it's better than
nothing.Theoretically, we could use the search index, but more work must be done
to make that happen.We could also, theoretically, use the full-text search feature for
MySQL, but not all versions support that for InnoDB. Work would need to
be done to introspect whether we can use full-text search, and it would
require hand-applying some SQL to the database. It also wouldn't buy us
anything for other types of databases.This may limit the usefulness of quick search for summaries until we
come up with something clever, but at least it won't break servers.
Tested search locally. It didn't check summaries until I entered my 5th
character. Then it did prefix matching instead of searching within text.Tested the query with MySQL using EXPLAIN, and saw that it was using the
index and substantially limited the number of rows it had to search.
- Change Summary:
-
Fixed an indentation warning.
- Commit:
-
963ff33f50da3c1dbc6b9070ac457d3ce8b4a1b85bef639e3f271cfe153738a34fea0c69120700df
-
Tool: Pyflakes Processed Files: reviewboard/reviews/evolutions/__init__.py reviewboard/webapi/resources/search.py reviewboard/reviews/models/base_review_request_details.py Tool: PEP8 Style Checker Processed Files: reviewboard/reviews/evolutions/__init__.py reviewboard/webapi/resources/search.py reviewboard/reviews/models/base_review_request_details.py
- Change Summary:
-
- Reduced the required number of characters to 4.
- Added the missing evolution file.
- Description:
-
Search for summaries in the quick search is very slow, for a couple of
reasons. We didn't have an index on the summary, but even if we did, we were looking for text in the middle of the summary field, which indexes can't help with. This could cause real problems on large installs, slowing down the
database making it nearly impossible to find what you're looking for. Continuing to type would trigger further expensive queries, eventually resulting in other queries stalling on the server. To fix this, we now have an index, and we now search only the beginnings
~ of summaries. We also only start searching if we have 5 or more ~ of summaries. We also only start searching if we have 4 or more characters. It limitation on where we search sucks, but it's better than nothing. Theoretically, we could use the search index, but more work must be done
to make that happen. We could also, theoretically, use the full-text search feature for
MySQL, but not all versions support that for InnoDB. Work would need to be done to introspect whether we can use full-text search, and it would require hand-applying some SQL to the database. It also wouldn't buy us anything for other types of databases. This may limit the usefulness of quick search for summaries until we
come up with something clever, but at least it won't break servers. - Commit:
-
5bef639e3f271cfe153738a34fea0c69120700df2d2f812e663430b80fbb2dbc51ca849de3be09c3
-
Tool: Pyflakes Processed Files: reviewboard/reviews/evolutions/__init__.py reviewboard/webapi/resources/search.py reviewboard/reviews/evolutions/review_request_summary_index.py reviewboard/reviews/models/base_review_request_details.py Tool: PEP8 Style Checker Processed Files: reviewboard/reviews/evolutions/__init__.py reviewboard/webapi/resources/search.py reviewboard/reviews/evolutions/review_request_summary_index.py reviewboard/reviews/models/base_review_request_details.py