Take the 2-minute tour ×

I'm building a complex multi-table MySQL query, and even though it works, I'm wondering could I make it more simple.

The idea behind it is this, using the Events table that logs all site interaction, select the ID, Title, and Slug of the 10 most popular blog posts, and order by the most hits descending.

SELECT content.id, content.title, content.slug, COUNT(events.id) AS hits 
FROM content, events
WHERE events.created >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    AND events.page_url REGEXP  '^/posts/[0-9]'
    AND content.id = events.content_id
GROUP BY content.id

Blog post URLs have the following format:


As I mentioned it seems to work, but I'm sure I could be doing this cleaner.


share|improve this question
You should use ANSI JOIN syntax, but other than that it looks pretty simple to me. –  Barmar May 16 '13 at 0:08
Aside from not using JOIN, the REGEXP is the ugly part to me. It'd be nicer if the events table could categorize events by type, so you'd just be looking for something like events.type = 'post' or something. (I'm kinda guessing as to why you even need to look at page_url in the first place. Presumably there are events for the same piece of content that should not be counted.) –  grossvogel May 16 '13 at 0:11
add comment

2 Answers

The condition on created and the condition on page_url are both range conditions. You can get index-assistance for only one range condition per table in a SQL query, so you have to pick one or the other to index.

I would create an index on the events table over two columns (content_id, created).

ALTER TABLE events ADD KEY (content_id, created);

I'm assuming that restricting by created date is more selective than restricting by page_url, because I assume "/posts/" is going to match a large majority of the events.

After narrowing down the matching rows by created date, the page-url condition will have to be handled by the SQL layer, but hopefully that won't be too inefficient.

There is no performance difference between SQL-89 ("comma-style") join syntax and SQL-92 JOIN syntax. I do recommend SQL-92 syntax because it's more clear and it supports outer joins, but performance is not a reason to use it. The SQL query optimizer supports both join styles.

Temporary table and filesort are often costly for performance. This query is bound to create a temporary table and use a filesort, because you're using GROUP BY and ORDER BY against different columns. You can only hope that the temp table will be small enough to fit within your tmp_table_size limit (or increase that value). But that won't help if content.title or content.slug are BLOB/TEXT columns, the temp table will be forced to be spooled on disk anyway.

share|improve this answer
add comment

Instead of a regular expression, you can use the left function:

SELECT content.id, content.title, content.slug, COUNT(events.id) AS hits FROM content JOIN events ON content.id = events.content_id
            AND left( events.page_url, 7) = '/posts/'
            GROUP BY content.id
            ORDER BY hits DESC
            LIMIT 10)

But that's just off the top of my head, and without a fiddle, untested. The JOIN suggestion, made in the comment, is also good and has been reflected in my answer.

share|improve this answer
The ^ in the REGEXP implies that it must begin /posts/, so if you are going to do it this way (not enforcing a digit), it may be a lot faster to use LEFT(events.page_url, 7) = '/posts/ –  Michael Berkowski May 16 '13 at 0:29
Quite right, updated to use left –  hd1 May 16 '13 at 0:33
since we're changing the question and inferring the date portion is not important... i think like would be even cheaper events.page_url like '/posts/%' –  gillyspy May 16 '13 at 2:04
I do think you're wrong about that, gillyspy –  hd1 May 16 '13 at 14:09
add comment

Your Answer


