I get an error for the below query:
SELECT
mt.tag_id,
count(mt.tag_id) as bcount,
bcount / t.count as rel,
t.count as t
FROM
tags.media_tag as mt,
tags.tags as t
WHERE
mt.media_id in (SELECT
mt.media_id
FROM
tags.media_tag as mt
WHERE
mt.tag_id = 'tag')
GROUP BY mt.tag_id
ORDER BY rel
LIMIT 1000;
Error:
Error Code: 1054. Unknown column 'bcount' in 'field list'
I'd like to use the count in the select again as it is quite an expensive query. I can just do count(mt.tag_id)
but that seems to be unbearably slow. Any ideas to optimize or achieve alias with the count?
Tables:
medias
(shown for completeness, not needed in query)
+----+-------+-------+---------+
| id | attr1 | attr2 | attrEtc |
+----+-------+-------+---------+
| 11 | | | |
| 22 | | | |
+----+-------+-------+---------+
media_tag
+----------+--------+
| media_id | tag_id |
+----------+--------+
| 11 | tag |
+----------+--------+
tags
+-----+-------+
| id | count |
+-----+-------+
| tag | 1 |
+-----+-------+
UPDATE: Sample Data: http://sqlfiddle.com/#!2/b8085
Expected Response where target tag, or in query above mt.tag_id = tagB
:
+--------+--------+--------+-----------------+
| tag_id | tcount | bcount | bcount / tcount |
+--------+--------+--------+-----------------+
| tagB | 2 | 2 | 1 |
| tagC | 2 | 1 | 0.5 |
| tagA | 3 | 1 | 0.333 |
+--------+--------+--------+-----------------+
bcount = number of shared media_id between tag_id and tagB (target)
This is the query I ended up using. @NoDisplayName got close but not all the way there. This SQL actually completes running in seconds and is much much faster than my original query.
SELECT
mt.tag_id,
COUNT(mt.tag_id) / t.count as rel,
COUNT(mt.tag_id) AS bcount,
t.count as tcount
FROM
tags.media_tag AS mt
INNER JOIN
(SELECT
smt.media_id
FROM
tags.media_tag as smt
WHERE
tag_id = 'tag') meds ON mt.media_id = meds.media_id
INNER JOIN
tags.tags t ON t.id = mt.tag_id
GROUP BY mt.tag_id
ORDER BY rel;