MySQL Views to the Rescue!

I was recently presented with the challenge of aggregating database hits for content that is e-mailed and commented on.  My gut reaction was to think of a PHP script, or perhaps set of scripts to filter & count the database rows.  Script processing of data sets with proper ORM can yield very meaningful information to both the programmer and the end-user.  However this can be memory-intensive, and creating an ORM layer for such a simple task *could* be considered overkill.  This is when MySQL views can help you.

So let’s say you have a database table called submissions that contains a content_id field, a submit_type (comment form, e-mail form, etc.), and some other info like user_email.  A way to aggregate these records by content might be to do the following:

SELECT COUNT(DISTINCT user_email)
FROM submissions
WHERE submit_type='email'
AND content_id=20

This should return a count of e-mail form submissions that have distinct senders.  The same e-mail address will not be counted twice.  This could be a good measure against spammers.

The next step is to aggregate for all content, after all we’re not looking for just one record!  This should do the trick:

SELECT COUNT(DISTINCT user_email) total, content_id content
FROM submissions
WHERE submit_type='email'
AND content_id IS NOT NULL
GROUP BY content_id
ORDER BY content_id DESC

This returns aggregates of distinct senders (DISTINCT) per content id (GROUP BY) for all valid content (IS NOT NULL), and then orders the results starting with the latest content (ORDER BY … DESC).

Now the final step – run a CREATE VIEW query with the aggregation:

CREATE VIEW content_emailed (total, content) AS
SELECT COUNT(DISTINCT user_email), content_id
FROM submissions
WHERE submit_type='email'
AND content_id IS NOT NULL
GROUP BY content_id
ORDER BY content_id DESC

Your database will now contain a view called content_emailed that will automatically update with each new form submit.  You can now also query easily which articles are e-mailed the most (ORDER BY total DESC), or just select a count per article (SELECT total FROM content_emailed WHERE content=20 — how’s that compared to the first query??).  It really couldn’t be any easier!