Refactor search

Issue description

The search gets slow in large systems. This might be due to unfortunate joins in the query.

Developer comments

The LIMIT clause slows it down big time: with limit 2 seconds, without 0.6 seconds.

The average response time for search in the past 5 days for 2 large systems before refactoring: * System Cargo: 1.38 seconds * System Medien: 2.25 seconds

After the first optimization (removing the LIMIT), the search is faster. 5 days work days in production yields these averages: * System Cargo: 0.51 seconds * System Medien: 0.45 seconds

Reporting date

2024-02-03

Reported by

Robert Cerny

Resolving date

2024-02-09

Resolved by

Robert Cerny

Resolved in version

Topincs 9.4.0

Sub issues

Unlabeled topics(Change)

Related to3

Large system: slow range retrieval(Bug)

Label table per language(Change)

Search slower again(Bug)

Caused

Empty index(Bug)

Work sessions

Start	2024-02-03T10:13:36
End	2024-02-03T13:50:14
Participant	Robert Cerny

Helpful webpages

stackoverflow.com/…using-limit-in-my-query