From a70fc447652c92c3f19651c1ad27b5a31852b077 Mon Sep 17 00:00:00 2001 From: Přemysl Eric Janouch Date: Wed, 27 Dec 2023 19:46:43 +0100 Subject: Optimize search query --- main.go | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) diff --git a/main.go b/main.go index abb5994..971391e 100644 --- a/main.go +++ b/main.go @@ -968,11 +968,16 @@ const searchCTE = `WITH JOIN matches AS m ON m.sha1 = ta.sha1 ), scoredtags(tag, score) AS ( + -- The cross join is a deliberate optimization, + -- and this query may still be really slow. SELECT st.tag, AVG(IFNULL(ta.weight, 0)) AS score - FROM supertags AS st, matches AS m + FROM matches AS m + CROSS JOIN supertags AS st LEFT JOIN tag_assignment AS ta ON ta.sha1 = m.sha1 AND ta.tag = st.tag GROUP BY st.tag + -- Using the column alias doesn't fail, but it also doesn't work. + HAVING AVG(IFNULL(ta.weight, 0)) >= 0.01 ) ` -- cgit v1.2.3-70-g09d2