[backend] severe performance problem in listruns API

[juliusvonkohout]

This is a followup of #6845 from @difince @zijianjoy
The listruns query is also very slow (seconds for large run_details tables).

It must also be migrated to joins or something other more performant

Already this small database leads to 15 seconds per listruns api call
It regressed from 5 to 15 seconds with Kubeflow 1.5 -> 1.7.

mysql> SELECT
    ->     namespace,
    ->     COUNT(*) AS entry_count,
    ->     SUM(LENGTH(CAST(namespace AS CHAR))) AS column_size
    -> FROM
    ->     run_details
    -> GROUP BY
    ->     namespace
    -> ORDER BY
    ->     column_size DESC;
+------------------------------------------------+-------------+-------------+
| namespace         | entry_count | column_size |
+------------------------------------------------+-------------+-------------+
| namespace-xxx     |       93931 |     1033241 |
| namespace-yyy     |       22645 |      362320 |
| namespace-zzz     |       16700 |      317300 |
| namespace-www     |        9894 |      187986 

Steps to reproduce

Just let users run a lot of pipelines per year, e.g. 200,000 and see how slow the listruns query becomes.

Expected result

Listruns in less than one second for 200,000 runs, which is a very small database task

Materials and Reference

Impacted by this bug? Give it a 👍.

[juliusvonkohout]

Solved and closed see #9780