Dear all:
We are trying to optimize query with metabase and have some performance related question about filters.
When we add a filter that correspond to "brandId", there's no join used. But when we use "brandId->name", a join is added to the query (see below). Is there a way to get grid of this join? As the "brandId" is already known (otherwise UI won't able to show the name), the optimal query will be just use where brandId = {{brandId}} instead of using a join to lookup the id with the name.
Alternatively, is there a way to add a dashboard filter that doesn't use join, but show brand.name alongs with brandId as value for filtering?
Thanks any help much appreciated!
Result query:
SELECT date (Order.business) AS business, count (*) AS count, sum (Order.grandTotal) AS sum
FROM Order
LEFT JOIN Group Group__via__groupId ON Order.groupId = Group__via__groupId.id LEFT JOIN Brand Brand__via__brandId ON Order.brandId = Brand__via__brandId.id
WHERE (Order.tally = TRUE
AND Group__via__groupId.name = 'group name' AND Brand__via__brandId.name = 'brand name' AND Order.partition >= convert_tz('2022-07-01 00:00:00.000', 'Etc/GMT+0', 'Etc/GMT+0') AND Order.partition < convert_tz('2022-08-01 00:00:00.000', 'Etc/GMT+0', 'Etc/GMT+0'))
GROUP BY date (Order.business)
ORDER BY date (Order.business) ASC
Optimal query:
SELECT date (Order.business) AS business, count (*) AS count, sum (Order.grandTotal) AS sum
FROM Order
WHERE (Order.tally = TRUE and Order.brandId = '100053' and Order.groupId = '10000'
AND Order.partition >= convert_tz('2022-07-01 00:00:00.000', 'Etc/GMT+0', 'Etc/GMT+0') AND Order.partition < convert_tz('2022-08-01 00:00:00.000', 'Etc/GMT+0', 'Etc/GMT+0'))
GROUP BY date (Order.business)
ORDER BY date (Order.business) ASC