Hi,
I have set up a dashboard with various questions that utilise roughly the same query (with different GROUP BYs), querying a view and LEFT JOINING on a table in MySQL. Something like this (this is for my “scoring” question):
WITH base_data AS (
SELECT
tags_snapshot.tag_value_1,
tags_snapshot.tag_value_2,
my_view.company_id,
my_view.scoring,
FROM
my_view
LEFT JOIN tags_snapshot
ON tags_snapshot.list_id = my_view.list_id
AND tags_snapshot.company_id = my_view.company_id
WHERE 1=1
AND my_view.list_id = {{list_id}}
[[AND {{datapoint_1_value}}]]
[[AND {{datapoint_2_value}}]]
<other field filters on my_view here>
)
SELECT
MAX(base_data.scoring) AS Scoring,
COUNT(DISTINCT mc_company_id) AS `Number of Companies`
FROM base_data
GROUP BY base_data.company_id
HAVING Scoring IS NOT NULL
ORDER BY Scoring;
The questions themselves load fine, as do most of the filters at the dashboard level, except for the filters (dropdown) related to the tag_values which are being populated from the table I’m LEFT JOINING on, tags_snapshot. Those filters are being aliased as datapoint_<x>_value and datapoint_<x> in my question and dashboard level filters, respectively.
For some reason, those filters (eg: “datapoint_1”) basically don’t populate dropdown values and time out whenever they are linked to my list_id ID filter, which is required to be specified and linked to all the other filters in my dashboard. If I unlink them, they load fine. I was able to locate the query that Metabase uses to generate the dropdown values and it seems quite inefficient:
SELECT
ats.tag_value_1 AS tag_value_1
FROM
tags_snapshot AS ats
LEFT JOIN (
SELECT
list_id,
company_id,
<many_other_fields>,
datapoint_1 <---- why is this here?
FROM
my_view
) AS am
ON ats.list_id = am.list_id
WHERE
am.list_id = <x>
GROUP BY
ats.tag_value_1
ORDER BY
ats.tag_value_1 ASC
LIMIT 1000;
Does anybody have insight into why a query with a linked filter would be so much slower than without? In theory there should be less data to scan through, even though the query itself might be slightly slower.
PS: I’ve tried different configuration of my base SQL query powering the questions, eg: querying on the tags_snapshot table and RIGHT JOINing on my_view, but that didn’t help.
I’ve also reviewed all the question-level filters for my datapoint_<x>_value and made sure they are configured as field filters, string widget type, dropdown list and multiple values.
