Search code examples
mysqlsqlsql-optimization

How to use COUNT alias in SELECT?


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)


Solution

  • 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;