Faster query for emoji occurences

[ajkeast]

Current query iterates over every message looking for 51 possible emojis. Takes 2+ seconds for query to complete

SELECT
    e.id,
    e.emoji_name,
    e.url,
    e.created_at,
    e.last_updated,
    COALESCE(occurences, 0) AS occurrences
FROM
    emojis as e
LEFT JOIN (
    SELECT
        emojis.id,
        emojis.emoji_name,
        COUNT(*) AS occurences
    FROM
        emojis
    JOIN
        messages m ON LOCATE(CONCAT(':', emojis.emoji_name, ':'), m.content) > 0
    GROUP BY
        emojis.id
) AS subquery ON e.id = subquery.id;