Optimize search query

1 files changed, 6 insertions, 1 deletions

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
 	)
 `