Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Possible archiving query optimization #16916

Closed
diosmosis opened this issue Dec 9, 2020 · 1 comment
Closed

Possible archiving query optimization #16916

diosmosis opened this issue Dec 9, 2020 · 1 comment

Comments

@diosmosis
Copy link
Member

In Model::getArchiveIdAndVisits() we look for an archive ID and some related metrics in the archive. The query used can potentially scan thousands or more rows just to look for a couple bits of data, due mostly to us not being able to add name to the index.

A possible solution would be to use an approach similar to the log_action table and add a hash column that hashes together the idsite, period, date1, date2, name columns, index it and just query that column. We could potentially also just hash the done flag and add it to the existing index.

@tsteur
Copy link
Member

tsteur commented Jan 3, 2024

This query used to be very slow. We then changed the index from:

  KEY `index_idsite_dates_period` (`idsite`,`date1`,`date2`,`period`,`ts_archived`)

to

  KEY `index_idsite_dates_period` (`idsite`,`date1`,`date2`,`period`,`name`(6))

and it made the query a lot faster since it speeds up the name search for "done" flags.

@tsteur tsteur closed this as not planned Won't fix, can't repro, duplicate, stale Jan 3, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants