Anonymity Check on the dashboard takes extra time

[dave-mills]

When adding the code the check if there is enough data to present a fully "anonymised" set of "other" institutions' data, the dashboard query goes from ~150ms to ~2.5 seconds. Not huge, but worth exploring to see if it can be optimised.

[dave-mills]

working on branch optimise_anonymity_check

[dan-tang-ssd]

I have revised two SQLs, pushed latest code in branch optimise_anonymity_check.

I have cloned live db to my local db yesterday. Two revised SQLs took 13ms and 11ms to execute in my local db now.

Below please find BEFORE and AFTER for quick reference.

Some highlights:

1. dashboard_project tables may have multiple records for one project. E.g. one project with multiple countries. I use DISTINCT for a correct count of project id.
2. The long running time of second SQL may be related to:
   • LEFT JOIN (we will have more records in query result, I use INNER JOIN instead)
   • not limiting dashboard_project records to a particular dashboard ID

---

BEFORE

-- find number of projects SELECT COUNT(*) from dashboard_project where dashboard_id = 1687878621;

-- find number of organisations SELECT COUNT(DISTINCT (projects.organisation_id)) FROM dashboard_project LEFT JOIN projects on projects.id = dashboard_project.project_id;

---

AFTER

-- find number of projects SELECT COUNT(DISTINCT project_id) AS number_of_project FROM dashboard_project WHERE dashboard_id = 1687878621;

-- find number of organisations SELECT COUNT(DISTINCT p.organisation_id) AS number_of_organisation FROM dashboard_project AS dp, projects AS p WHERE dp.project_id = p.id AND dp.dashboard_id = 1687878621;

[dave-mills]

Amazing! Thanks - the Count DISTINCT and INNER JOIN are good things for me to remember in future. 👍