Skip to content

Instantly share code, notes, and snippets.

@wedgemartin
Created January 28, 2011 17:38
Show Gist options
  • Save wedgemartin/800612 to your computer and use it in GitHub Desktop.
Save wedgemartin/800612 to your computer and use it in GitHub Desktop.
Why is this query not hitting an index?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| activities | CREATE TABLE `activities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`ip` varchar(255) DEFAULT NULL,
`activity_type_id` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
`publisher_id` int(11) DEFAULT NULL,
`local_user_time` datetime DEFAULT NULL,
`user_badge_id` int(11) DEFAULT NULL,
`points` int(11) DEFAULT '0',
`behavior_id` int(11) DEFAULT NULL,
`event_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_activities_on_user_id_and_publisher_id_and_behavior_id` (`user_id`,`publisher_id`,`behavior_id`),
KEY `index_activities_on_created_at_and_user_id_and_publisher_id` (`created_at`,`user_id`,`publisher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8412 DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain SELECT sum(`activities`.points) AS sum_points FROM `activities` WHERE (`activities`.`user_id` = 2688 AND `activities`.`publisher_id` = 2);
+----+-------------+------------+------+--------------------------------------------------------------+--------------------------------------------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+--------------------------------------------------------------+--------------------------------------------------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | activities | ref | index_activities_on_user_id_and_publisher_id_and_behavior_id | index_activities_on_user_id_and_publisher_id_and_behavior_id | 10 | const,const | 1 | Using where |
+----+-------------+------------+------+--------------------------------------------------------------+--------------------------------------------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment