fix: Improve mrna/genotype unlinked data queries to avoid including full dataset list in query HEAD main

Previously the full list of datasets (currently over 900) was included in the query. This commit instead handles the exclusion at the Python level, dramatically speeding up the query

2 files changed, 54 insertions, 49 deletions

diff --git a/gn_auth/auth/authorisation/data/genotypes.py b/gn_auth/auth/authorisation/data/genotypes.py
index d44cbfb..17f3317 100644
--- a/gn_auth/auth/authorisation/data/genotypes.py
+++ b/gn_auth/auth/authorisation/data/genotypes.py
@@ -32,12 +32,21 @@ def ungrouped_genotype_data(# pylint: disable=[too-many-arguments, too-many-posi
         search_query: str, selected: tuple[dict, ...] = tuple(),
         limit: int = 10000, offset: int = 0) -> tuple[
             dict, ...]:
-    """Retrieve genotype data that is not linked to any user group."""
-    params = tuple(
-        (row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"])
-            for row in linked_genotype_data(authconn)) + tuple(
-                    (row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"])
-                    for row in selected)
+    """Retrieve genotype data that is not linked to any user group.
+
+    The set of linked datasets is read from the auth database (SQLite) and the
+    exclusion happens in Python. This avoids embedding the (ever-growing) list
+    of linked datasets into the MariaDB query as a giant `NOT IN` list, which
+    MariaDB executes with an unindexed nested-loop join and which degrades
+    badly as more datasets get linked.
+    """
+    def __key__(row):
+        """Normalise a row's dataset identity to a comparable tuple."""
+        return (int(row["SpeciesId"]), int(row["InbredSetId"]),
+                int(row["GenoFreezeId"]))
+
+    excluded = {__key__(row) for row in linked_genotype_data(authconn)} | {
+        __key__(row) for row in selected}
     query = (
         "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, "
         "gf.Id AS GenoFreezeId, gf.Name AS dataset_name, "
@@ -46,27 +55,21 @@ def ungrouped_genotype_data(# pylint: disable=[too-many-arguments, too-many-posi
         "FROM Species AS s INNER JOIN InbredSet AS iset "
         "ON s.SpeciesId=iset.SpeciesId INNER JOIN GenoFreeze AS gf "
         "ON iset.InbredSetId=gf.InbredSetId ")
-
-    if len(params) > 0 or bool(search_query):
-        query = query + "WHERE "
-
-    if len(params) > 0:
-        paramstr = ", ".join(["(%s, %s, %s)"] * len(params))
-        query = query + (
-            "(s.SpeciesId, iset.InbredSetId, gf.Id) "
-            f"NOT IN ({paramstr}) "
-            ) + ("AND " if bool(search_query) else "")
-
+    params = tuple()
     if bool(search_query):
         query = query + (
-            "CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) LIKE %s ")
-        params = params + ((f"%{search_query}%",),)# type: ignore[operator]
+            "WHERE CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) "
+            "LIKE %s ")
+        params = (f"%{search_query}%",)
 
-    query = query + f"LIMIT {int(limit)} OFFSET {int(offset)}"
     with gn3conn.cursor(DictCursor) as cursor:
-        cursor.execute(
-            query, tuple(item for sublist in params for item in sublist))
-        return tuple(row for row in cursor.fetchall())
+        cursor.execute(query, params)
+        _rows = tuple(row for row in cursor.fetchall())
+
+    # Filter out linked/selected datasets and apply pagination in Python.
+    return tuple(
+        row for row in _rows
+        if __key__(row) not in excluded)[offset:offset + limit]
 
 @authorised_p(
     ("system:data:link-to-group",),
diff --git a/gn_auth/auth/authorisation/data/mrna.py b/gn_auth/auth/authorisation/data/mrna.py
index fcf6ea3..1af0c41 100644
--- a/gn_auth/auth/authorisation/data/mrna.py
+++ b/gn_auth/auth/authorisation/data/mrna.py
@@ -33,14 +33,21 @@ def ungrouped_mrna_data(# pylint: disable=[too-many-arguments, too-many-position
         search_query: str, selected: tuple[dict, ...] = tuple(),
         limit: int = 10000, offset: int = 0) -> tuple[
             dict, ...]:
-    """Retrieve mrna data that is not linked to any user group."""
-    params = tuple(
-        (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"],
-         row["ProbeSetFreezeId"])
-        for row in linked_mrna_data(authconn)) + tuple(
-                (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"],
-                 row["ProbeSetFreezeId"])
-                for row in selected)
+    """Retrieve mrna data that is not linked to any user group.
+
+    The set of linked datasets is read from the auth database (SQLite) and the
+    exclusion happens in Python. This avoids embedding the (ever-growing) list
+    of linked datasets into the MariaDB query as a giant `NOT IN` list, which
+    MariaDB executes with an unindexed nested-loop join and which degrades
+    badly as more datasets get linked.
+    """
+    def __key__(row):
+        """Normalise a row's dataset identity to a comparable tuple."""
+        return (int(row["SpeciesId"]), int(row["InbredSetId"]),
+                int(row["ProbeFreezeId"]), int(row["ProbeSetFreezeId"]))
+
+    excluded = {__key__(row) for row in linked_mrna_data(authconn)} | {
+        __key__(row) for row in selected}
     query = (
         "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, "
         "pf.ProbeFreezeId, pf.Name AS StudyName, psf.Id AS ProbeSetFreezeId, "
@@ -49,27 +56,22 @@ def ungrouped_mrna_data(# pylint: disable=[too-many-arguments, too-many-position
         "FROM Species AS s INNER JOIN InbredSet AS iset "
         "ON s.SpeciesId=iset.SpeciesId INNER JOIN ProbeFreeze AS pf "
         "ON iset.InbredSetId=pf.InbredSetId INNER JOIN ProbeSetFreeze AS psf "
-        "ON pf.ProbeFreezeId=psf.ProbeFreezeId ") + (
-            "WHERE " if (len(params) > 0 or bool(search_query)) else "")
-
-    if len(params) > 0:
-        paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(params))
-        query = query + (
-            "(s.SpeciesId, iset.InbredSetId, pf.ProbeFreezeId, psf.Id) "
-            f"NOT IN ({paramstr}) "
-            ) + ("AND " if bool(search_query) else "")
-
+        "ON pf.ProbeFreezeId=psf.ProbeFreezeId ")
+    params = tuple()
     if bool(search_query):
         query = query + (
-            "CONCAT(pf.Name, psf.Name, ' ', psf.FullName, ' ', psf.ShortName) "
-            "LIKE %s ")
-        params = params + ((f"%{search_query}%",),)# type: ignore[operator]
+            "WHERE CONCAT(pf.Name, psf.Name, ' ', psf.FullName, ' ', "
+            "psf.ShortName) LIKE %s ")
+        params = (f"%{search_query}%",)
 
-    query = query + f"LIMIT {int(limit)} OFFSET {int(offset)}"
     with gn3conn.cursor(DictCursor) as cursor:
-        cursor.execute(
-            query, tuple(item for sublist in params for item in sublist))
-        return tuple(row for row in cursor.fetchall())
+        cursor.execute(query, params)
+        _rows = tuple(row for row in cursor.fetchall())
+
+    # Filter out linked/selected datasets and apply pagination in Python.
+    return tuple(
+        row for row in _rows
+        if __key__(row) not in excluded)[offset:offset + limit]
 
 @authorised_p(
     ("system:data:link-to-group",),
@@ -120,7 +122,7 @@ def resources_by_datasets_and_traits(
         },
         dsets_traits,
         {})
-    paramstr = ", ".join(["?"] * len(dsets_traits))
+    paramstr = ", ".join(["?"] * len(traits_by_datasets.keys()))
     query = (
         "SELECT r.*, rc.*, lmd.dataset_name FROM linked_mrna_data AS lmd "
         "INNER JOIN mrna_resources AS mr ON lmd.data_link_id=mr.data_link_id "