Skip to content

perf: tweak some search queries

Mario Oenning requested to merge moson/aurweb:tweak-search-queries into master

We currently sorting on two columns in different tables which is quite expensive in terms of performance:
MariaDB is first merging the data into some temporary table to apply the sorting and record limiting.

We can tweak a couple of these queries by changing the "order by" clause such that they refer to columns within the same table (PackageBases).
So instead performing the second sorting on "Packages.Name", we do this on "PackageBases.Name" instead. This should still be "good enough" to produce properly sorted results.

Some numbers with our default query sorted by Popularity:

Before (Execution time ~500ms)

image

After (Execution time ~65ms)

image

After index creation (Execution time ~2ms)

image

ToDo:

  • Add alembic migration creating an index to cover our default query and tweak things even further.
    CREATE INDEX BasesPopularityName USING BTREE ON aurweb.PackageBases (Popularity,Name);

Some background info: MySQL ORDER BY LIMIT Performance Optimization

Edited by Mario Oenning

Merge request reports