canonical-ubuntu-qa team mailing list archive

[Skia <mp+461729@xxxxxxxxxxxxxxxxxx>]

Skia has proposed merging ~hyask/autopkgtest-cloud:skia/additional_indexes into autopkgtest-cloud:master.

Requested reviews:
  Canonical's Ubuntu QA (canonical-ubuntu-qa)

For more details, see:
https://code.launchpad.net/~hyask/autopkgtest-cloud/+git/autopkgtest-cloud/+merge/461729
-- 
Your team Canonical's Ubuntu QA is requested to review the proposed merge of ~hyask/autopkgtest-cloud:skia/additional_indexes into autopkgtest-cloud:master.

diff --git a/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py b/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py
index 12d93b5..cf9ca27 100644
--- a/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py
+++ b/charms/focal/autopkgtest-web/webcontrol/helpers/utils.py
@@ -76,6 +76,14 @@ def init_db(path, **kwargs):
             "CREATE INDEX IF NOT EXISTS result_run_ix ON result("
             "  run_id desc)"
         )
+        # /admin mostly benefits from the index on test_id (~80s -> 50ms)
+        # /packages/<name> also sees some improvements (~14s -> 30ms)
+        c.execute(
+            "CREATE INDEX IF NOT EXISTS result_test_id_ix ON result(test_id);"
+        )
+        # exact same pages (/admin and /packages/<name>) goes from (~50ms to ~3ms)
+        # with this other index
+        c.execute("CREATE INDEX IF NOT EXISTS test_id_ix ON test(id);")
         db.commit()
         logging.debug("database %s created", path)
     except sqlite3.OperationalError as e: