Skip to content
  • Jared Hancock's avatar
    a9c283ec
    search: Refactor full-text search · a9c283ec
    Jared Hancock authored
    This patch adds a few changes. First, the full-text search query is
    refactored so that MySQL can focus first on finding matching records in
    its full-text search index (in the %_search table) in a subquery. Then
    it can focus on matching records against tickets in an outer query
    (whether ticket data, thread entries, user info, or organization inf was
    matched).  This dramatically speeds up the query for large datasets by
    about 20x (about three seconds instead of sixty-something).
    
    Secondly, results are now sorted based on relevance, so the best hits
    sort a the top of the list. This is accomplished by adding the
    `relevance` to the query sort list via the extra() method. Then, since
    no GROUP BY clause is really necessary, it is not added to the query,
    which will allow the results not to be re-sorted by the ticket_id. (That
    is, the remain sorted by the search relevance).
    
    Third, the relevance has a lower limit of 0.3. Good hits seem to have a
    much larger value (like hundreds), so setting a small value will help to
    remove hits with barely any relevance to the search terms.
    a9c283ec
    search: Refactor full-text search
    Jared Hancock authored
    This patch adds a few changes. First, the full-text search query is
    refactored so that MySQL can focus first on finding matching records in
    its full-text search index (in the %_search table) in a subquery. Then
    it can focus on matching records against tickets in an outer query
    (whether ticket data, thread entries, user info, or organization inf was
    matched).  This dramatically speeds up the query for large datasets by
    about 20x (about three seconds instead of sixty-something).
    
    Secondly, results are now sorted based on relevance, so the best hits
    sort a the top of the list. This is accomplished by adding the
    `relevance` to the query sort list via the extra() method. Then, since
    no GROUP BY clause is really necessary, it is not added to the query,
    which will allow the results not to be re-sorted by the ticket_id. (That
    is, the remain sorted by the search relevance).
    
    Third, the relevance has a lower limit of 0.3. Good hits seem to have a
    much larger value (like hundreds), so setting a small value will help to
    remove hits with barely any relevance to the search terms.
Loading