Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The interface shows all objects in order of decreasing importance (ie, hits).

The schema for the two tables (slightly trimmed down, but you get the gist):

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_desc` varchar(255) NOT NULL,
  PRIMARY KEY (`object_id`)
);

The query I'm having trouble with is below. It works fine with my table of ~100 entries, but the EXPLAIN worries me a little.

    explain SELECT 
            el.object_id, 
            leo.object_desc, 
            COUNT(el.object_id) as count_rows
        FROM 
            event_log el 
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY 
            el.object_id
        ORDER BY 
            count_rows DESC,
            leo.object_desc ASC

Returns: Using index; Using temporary; Using filesort

So -- what's wrong with my schema and/or query for MySQL to fall back on temporary and filesort? Or is it as optimized as it can get using ORDER BY?

share|improve this question
    
Try changing COUNT(el.object_id) into COUNT(*). You'll probably have a small efficiency gain. Other than that, the query looks good. Ordering by a calculated column cannot be super efficient anyway. –  ypercube Nov 30 '12 at 2:46
    
You could try rewriting it (group by in a subquery with only event_log table) and then join to the lookup_event_objects. –  ypercube Nov 30 '12 at 2:48

1 Answer 1

up vote 2 down vote accepted

Here is your original query

SELECT 
    el.object_id, 
    leo.object_desc, 
    COUNT(el.object_id) as count_rows
FROM 
    event_log el 
    LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY 
    el.object_id
ORDER BY 
    count_rows DESC,
    leo.object_desc ASC
;

It looks as if it is as optimized as it is going to to get

I would change you query

SELECT 
    el.object_id, 
    leo.object_desc, 
    SUM(ISNULL(leo.object_id)=0) as count_rows
FROM 
    event_log el 
    LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY 
    el.object_id
ORDER BY 
    count_rows DESC,
    leo.object_desc ASC
;

This might get you a more accurate count, especially for event logs with no lookup objects.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.