Improve performance when sorting tags with stats by visits or short URLs count

[acelaya]

Due to the complexity required on the underneath query used to list tags with stats, te performance when sorting by visits or short URLs count is not good.

A way to improve this would be to add some short-lived query result cache, which could be used to cache the result of a sepcific subset (limit+offset) with a specific set of conditions (API kye or not, search term or not, etc).

However, this could result in eventually inconsistent data, until the cache expires. Some ways to mitigate this:

• Allow to opt-in to this feature, so that there's no cache by default, and only when the amount of data grows then you can enable it knowing the side effects.
  • Users will have to enable redis or APCu.
• Implement some cache invalidation that clears it when a short URL is created or edited. Visits are not that important because are not created by admins, so the inconsistency might pass unnoticed until cache expires naturally.
  • Might be harder to achieve than it seems, as the cache key would need to include the tags it affects, and invalidation would have to apply for potentially multiple cache entries.
• Both of the above.

[acelaya]

Solutions already tried and discarded:

• Re-think query, using a different base table: Not possible, as when need to get all tags, always, so tags table has to be the main one.
• Use a view: Does not improve performance, as it still runs the query every time.
• Use an aggregate table that gets updated periodically (pseudo-cache):
  • It would be very complex to maintain, as values can change based on new visits, new short URLs, deleted short URLs and edited short URLs.
  • Depending on the API key permissions, the aggregate counts are different, and it's not possible to foresee all possible combinations to have them pre-calculated.
  • The migration filling-up this table might take long to run.
• Try to join instead of left-join: Performance is muuuuch lower.

Solutions not fully verified:

• Use a cross-table composite index: Not yet verified if all supported DB engines allow it.

[acelaya]

The approach used for the query has been recently changed. Now there are sub-queries calculating the visits count (and also the non-bot visits count).

Maybe it's possible to apply limit/offset to the new sub-queries, as it's done with the tags sub-query, and see if that improves performance.

In case it does, consider moving the calculation of the short URLs count also to a sub-query to use the same approach.

[acelaya]

New idea: Track amount of visits and short URLs per tag in a separate table which has a value that's incremented every time a new visit happens or a short URL is created.

That would remove the need to do aggregates at runtime (COUNT(...)), and should also drastically simplify the queries and remove the need to use sub-queries.

It has a big consideration though. In order to guarantee data consistency, those tables need to lock while being updated, and they will have to be updated on every visit. Servers with a lot of visits will get impacted on performance.

Ways to mitigate this:

• Have separate tables to count the amount of short URLs and the amount of visits, as the latter will probably update much more frequently.
• Spread the total value in multiple rows per tag, instead of just one. When the value needs to be incremented, we pick one of them randomly.
  That will significantly reduce the impact of database locking, and then we can get the actual total by doing an aggregate SUM() and all the partial totals for all rows that belong to the same tag.

EDIT: By reading #1346 (comment), I realized I already evaluated this option, but perhaps #2036 could be another alternative, or present some improvement.

[acelaya]

After implementing #2074, the overall performance has been drastically improved with #2078.

However, it still takes almost as much time regardless of how many tags are fetched. With the improvements of the new visits count table, the overall query could/should be reworked in order to address this.

[acelaya]

I'm going to close this as completed, as the most outstanding problem, very slow queries, should now be fixed.

In future, we can give this another round of thinking, and try to simplify the query, in an attempt to make the performance even better when fetching only a subset.