Uploaded image for project: 'Blesta Core'
  1. Blesta Core
  2. CORE-2670

Support Manager: When searching tickets, search all replies and notes

        Details

        • Type: Improvement
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 4.2.0
        • Fix Version/s: 5.0.0-b1
        • Component/s: Plugins
        • Labels:
          None

          Description

          When selecting "Ticket Search" in the global search bar, it appears as if only ticket summaries are searched. It would be ideal if we could additionally search ticket replies and notes.

          Perhaps some benchmarking is in order to ensure it's not ridiculously slow, but the search isn't as effective as it could be without including ticket reply text.

          Originally requested here https://requests.blesta.com/topic/search-content-of-tickets

            Activity

            Ascending order - Click to sort in descending order
            admin Paul Phillips created issue -
            Hide
            Permalink
            tyson Tyson Phillips (Inactive) added a comment -

            The ticket search only searches the ticket information, not reply information. We can search the ticket reply details too, but it will certainly have an impact on performance, especially for systems with lots of tickets. It may be useful to limit the search by a date (e.g. search only the last 1 year of tickets), but this will likely need to be configurable in some way

            Show
            tyson Tyson Phillips (Inactive) added a comment - The ticket search only searches the ticket information, not reply information. We can search the ticket reply details too, but it will certainly have an impact on performance, especially for systems with lots of tickets. It may be useful to limit the search by a date (e.g. search only the last 1 year of tickets), but this will likely need to be configurable in some way
            tyson Tyson Phillips (Inactive) made changes -
            Field Original Value New Value
            Issue Type Bug [ 1 ] Improvement [ 4 ]
            tyson Tyson Phillips (Inactive) made changes -
            Story Points 2
            tyson Tyson Phillips (Inactive) made changes -
            Sprint 4.4.0 Sprint 2 [ 59 ]
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked higher
            tyson Tyson Phillips (Inactive) made changes -
            Sprint 4.4.0 Sprint 3 [ 59 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4 [ 59, 71 ]
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked higher
            jonathan Jonathan Reissmueller made changes -
            Assignee Jonathan Reissmueller [ jonathan ]
            Automated transition triggered when Jonathan Reissmueller created a branch in Stash -
            Status Open [ 1 ] In Progress [ 3 ]
            jonathan Jonathan Reissmueller made changes -
            Remaining Estimate 0 minutes [ 0 ]
            Time Spent 50 minutes [ 3000 ]
            Worklog Id 11479 [ 11479 ]
            jonathan Jonathan Reissmueller made changes -
            Time Spent 50 minutes [ 3000 ] 55 minutes [ 3300 ]
            Worklog Id 11479 [ 11479 ]
            tyson Tyson Phillips (Inactive) made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4 [ 59, 71 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.4.0 Sprint 5 [ 59, 71, 72 ]
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked higher
            tyson Tyson Phillips (Inactive) made changes -
            Time Spent 55 minutes [ 3300 ] 2 hours, 15 minutes [ 8100 ]
            Worklog Id 11534 [ 11534 ]
            tyson Tyson Phillips (Inactive) made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.4.0 Sprint 5 [ 59, 71, 72 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4 [ 59, 71 ]
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked lower
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked higher
            tyson Tyson Phillips (Inactive) made changes -
            Fix Version/s 4.9.0-b1 [ 11301 ]
            Fix Version/s Short Term [ 10800 ]
            tyson Tyson Phillips (Inactive) made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4 [ 59, 71 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6 [ 59, 71, 104 ]
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked higher
            Hide
            Permalink
            jonathan Jonathan Reissmueller added a comment -

            It is ridiculously slow. At least doubles the query time and in some of my testing increased it by as much as 10x.

            Show
            jonathan Jonathan Reissmueller added a comment - It is ridiculously slow. At least doubles the query time and in some of my testing increased it by as much as 10x.
            jonathan Jonathan Reissmueller made changes -
            Time Spent 2 hours, 15 minutes [ 8100 ] 5 hours, 13 minutes [ 18780 ]
            Worklog Id 13298 [ 13298 ]
            Hide
            Permalink
            tyson Tyson Phillips (Inactive) added a comment -

            Do you have a breakdown of sample results and their query time when run from your machine?
            e.g.

            • 100 records - 0.005s
            • 1000 records - 0.01s
            • 10000 records - 0.05s
            • 100000 records - 0.1s
              etc.
            Show
            tyson Tyson Phillips (Inactive) added a comment - Do you have a breakdown of sample results and their query time when run from your machine? e.g. 100 records - 0.005s 1000 records - 0.01s 10000 records - 0.05s 100000 records - 0.1s etc.
            Hide
            Permalink
            admin Paul Phillips added a comment -

            If performance is a big concern, would it be possible to add a 2nd ticket search option?

            • "Ticket Search" - unchanged
            • "Ticket Search Extensive"
            Show
            admin Paul Phillips added a comment - If performance is a big concern, would it be possible to add a 2nd ticket search option? "Ticket Search" - unchanged "Ticket Search Extensive"
            Hide
            Permalink
            tyson Tyson Phillips (Inactive) added a comment -

            I'd be more inclined to try a full text search

            Show
            tyson Tyson Phillips (Inactive) added a comment - I'd be more inclined to try a full text search
            Hide
            Permalink
            jonathan Jonathan Reissmueller added a comment -

            Didn't take benchmarks at a lot of sizes, but my original db was probably around 100 tickets and ~.7s vs ~1.5 and the populated db was ~1 million tickets and ~1.5s vs 14s

            Show
            jonathan Jonathan Reissmueller added a comment - Didn't take benchmarks at a lot of sizes, but my original db was probably around 100 tickets and ~.7s vs ~1.5 and the populated db was ~1 million tickets and ~1.5s vs 14s
            Hide
            Permalink
            tyson Tyson Phillips (Inactive) added a comment -

            You still have those databases? I'll see if there is an update for a full text search we could test as well and compare performance.

            Show
            tyson Tyson Phillips (Inactive) added a comment - You still have those databases? I'll see if there is an update for a full text search we could test as well and compare performance.
            Hide
            Permalink
            jonathan Jonathan Reissmueller added a comment - - edited

            You want a dump of the support tables? I do

            Show
            jonathan Jonathan Reissmueller added a comment - - edited You want a dump of the support tables? I do
            Hide
            Permalink
            tyson Tyson Phillips (Inactive) added a comment -

            We can test it against your databases to start

            Show
            tyson Tyson Phillips (Inactive) added a comment - We can test it against your databases to start
            Hide
            Permalink
            tyson Tyson Phillips (Inactive) added a comment - - edited

            I updated this branch to search against a FULLTEXT index. This is much faster, hopefully you see a benefit too.

            For me, I have 200k replies with 80k tickets and the search page loads in 2.5 to 3 seconds.

            Also, I only have it searching replies, not replies/notes, nor all reply types since that requires an additional table join which adds another 1.5 seconds to page load time.

            I added the branch for v2.22.0 of the support manager (i.e. blesta v4.10+) because this migration takes a long time. About 35 seconds to create the fulltext index on those 200k records. This can pose a problem during upgrades (e.g. timeouts).

            Show
            tyson Tyson Phillips (Inactive) added a comment - - edited I updated this branch to search against a FULLTEXT index. This is much faster, hopefully you see a benefit too. For me, I have 200k replies with 80k tickets and the search page loads in 2.5 to 3 seconds. Also, I only have it searching replies, not replies/notes, nor all reply types since that requires an additional table join which adds another 1.5 seconds to page load time. I added the branch for v2.22.0 of the support manager (i.e. blesta v4.10+) because this migration takes a long time. About 35 seconds to create the fulltext index on those 200k records. This can pose a problem during upgrades (e.g. timeouts).
            tyson Tyson Phillips (Inactive) made changes -
            Time Spent 5 hours, 13 minutes [ 18780 ] 7 hours, 29 minutes [ 26940 ]
            Worklog Id 13325 [ 13325 ]
            tyson Tyson Phillips (Inactive) made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6 [ 59, 71, 104 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.9.0 Sprint 7 [ 59, 71, 104, 105 ]
            tyson Tyson Phillips (Inactive) made changes -
            Rank Ranked higher
            tyson Tyson Phillips (Inactive) made changes -
            Fix Version/s 4.10.0-b1 [ 11305 ]
            Fix Version/s 4.9.0-b1 [ 11301 ]
            jonathan Jonathan Reissmueller made changes -
            Rank Ranked lower
            jonathan Jonathan Reissmueller made changes -
            Time Spent 7 hours, 29 minutes [ 26940 ] 1 day, 2 minutes [ 28920 ]
            Worklog Id 13357 [ 13357 ]
            Automated transition triggered when Jonathan Reissmueller created pull request #68 in Stash -
            Status In Progress [ 3 ] In Review [ 5 ]
            Resolution Fixed [ 1 ]
            abdy Abdy Franco made changes -
            Time Spent 1 day, 2 minutes [ 28920 ] 1 day, 7 minutes [ 29220 ]
            Worklog Id 13384 [ 13384 ]
            Automated transition triggered when Jonathan Reissmueller merged pull request #68 in Stash -
            Status In Review [ 5 ] Closed [ 6 ]
            jonathan Jonathan Reissmueller made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Reopened [ 4 ]
            Hide
            Permalink
            jonathan Jonathan Reissmueller added a comment -

            The fulltext index is supported in mysql 5.6+ and mariadb 10.0.5+ which are above the Blesta minimum requirements. We'll need to remove these changes and re-add them when we bump up the minimum requirements.

            Show
            jonathan Jonathan Reissmueller added a comment - The fulltext index is supported in mysql 5.6+ and mariadb 10.0.5+ which are above the Blesta minimum requirements. We'll need to remove these changes and re-add them when we bump up the minimum requirements.
            Automated transition triggered when Jonathan Reissmueller created a branch in Stash -
            Status Reopened [ 4 ] In Progress [ 3 ]
            Automated transition triggered when Jonathan Reissmueller created pull request #73 in Stash -
            Status In Progress [ 3 ] In Review [ 5 ]
            Resolution Fixed [ 1 ]
            jonathan Jonathan Reissmueller made changes -
            Fix Version/s 5.0.0-b1 [ 11107 ]
            Fix Version/s 4.10.0-b1 [ 11305 ]
            Automated transition triggered when Jonathan Reissmueller merged pull request #73 in Stash -
            Status In Review [ 5 ] Closed [ 6 ]
            Hide
            Permalink
            abdy Abdy Franco added a comment -

            I think this issue should remain open, as it was postponed for a future version.

            Show
            abdy Abdy Franco added a comment - I think this issue should remain open, as it was postponed for a future version.
            jonathan Jonathan Reissmueller made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Reopened [ 4 ]
            Automated transition triggered when Jonathan Reissmueller created pull request #74 in Stash -
            Status Reopened [ 4 ] In Review [ 5 ]
            Resolution Fixed [ 1 ]
            Automated transition triggered when Abdy Franco merged pull request #74 in Stash -
            Status In Review [ 5 ] Closed [ 6 ]
            abdy Abdy Franco made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Reopened [ 4 ]
            jonathan Jonathan Reissmueller made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1 [ 59, 71, 104, 105 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 1 [ 59, 71, 104, 105, 117 ]
            jonathan Jonathan Reissmueller made changes -
            Rank Ranked lower
            jonathan Jonathan Reissmueller made changes -
            Assignee Jonathan Reissmueller [ jonathan ]
            jonathan Jonathan Reissmueller made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 1 [ 59, 71, 104, 105, 117 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 2 [ 59, 71, 104, 105, 118 ]
            jonathan Jonathan Reissmueller made changes -
            Rank Ranked lower
            jonathan Jonathan Reissmueller made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 1 [ 59, 71, 104, 105, 118 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 2 [ 59, 71, 104, 105, 119 ]
            jonathan Jonathan Reissmueller made changes -
            Sprint 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 2 [ 59, 71, 104, 105, 119 ] 4.4.0 Sprint 3, 4.4.0 Sprint 4, 4.9.0 Sprint 6, 4.10.0 Sprint 1, 5.0.0 Sprint 3 [ 59, 71, 104, 105, 120 ]
            Automated transition triggered when Jonathan Reissmueller created a branch in Stash -
            Status Reopened [ 4 ] In Progress [ 3 ]
            Automated transition triggered when Jonathan Reissmueller created pull request #95 in Stash -
            Status In Progress [ 3 ] In Review [ 5 ]
            Resolution Fixed [ 1 ]
            jonathan Jonathan Reissmueller made changes -
            Time Spent 1 day, 7 minutes [ 29220 ] 1 day, 12 minutes [ 29520 ]
            Worklog Id 14363 [ 14363 ]
            Automated transition triggered when Jonathan Reissmueller merged pull request #95 in Stash -
            Status In Review [ 5 ] Closed [ 6 ]

              People

              • Assignee:
                Unassigned
                Reporter:
                admin Paul Phillips
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  23/Dec/20

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 12 minutes
                  1d 12m

                    Agile