Re: Why MySQL does not use an index?
Thanks for your answer. At first read, I thought that your answer could solve my problem. However, I have to analyse the situation even further and ask you to help me explain the following (which may contradict what you are saying, at least at first read too).
The show create table command on PRODUCTION machine, where I do have the problem outputs the following:
CREATE TABLE `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`data` text COLLATE utf8_unicode_ci,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`assessment_session_id` int(11) NOT NULL,
`device_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`device_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`credit_card_hash` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `events_assessment_sessions_fk` (`assessment_session_id`),
KEY `events_type_idx` (`type`),
KEY `events_device_ip_idx` (`device_ip`),
KEY `events_credit_card_hash_idx` (`credit_card_hash`),
KEY `events_device_id_idx` (`device_id`),
KEY `events_created_at_idx` (`created_at`),
CONSTRAINT `events_assessment_sessions_fk` FOREIGN KEY (`assessment_session_id`) REFERENCES `assessment_sessions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4414304 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
I checked and this is EXACTLY the same with my DEVELOPMENT machine. Now ... MySQL explain plan displays different things from PRODUCTION to DEVELOPMENT. Here it is:
DEVELOPMENT:
explain extended SELECT COUNT(`events`.`id`) FROM `events` WHERE `events`.`type` = 'CheckoutEvent' AND `events`.`device_ip` = '62.103.172.179' AND (events.created_at > '2012-01-14 07:33:45');
+----+-------------+--------+-------+------------------------------------------------------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+-------+------------------------------------------------------------+-----------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | events | range | events_type_idx,events_device_ip_idx,events_created_at_idx | events_created_at_idx | 9 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+-------+------------------------------------------------------------+-----------------------+---------+------+------+----------+-------------+
PRODUCTION:
+----+-------------+--------+-------------+------------------------------------------------------------+--------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+-------------+------------------------------------------------------------+--------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
| 1 | SIMPLE | events | index_merge | events_type_idx,events_device_ip_idx,events_created_at_idx | events_device_ip_idx,events_type_idx | 768,767 | NULL | 1655 | 75.05 | Using intersect(events_device_ip_idx,events_type_idx); Using where |
+----+-------------+--------+-------------+------------------------------------------------------------+--------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
Do you see that? On my DEVELOPMENT machine, exactly the same query uses the created_at index, whereas on production it does not.
Note that my DEVELOPMENT machine has MySQL:
> Server version: 5.1.57-3-log (Debian)
whereas my PRODUCTION machine has MySQL:
> Server version: 5.1.49-3-log (Debian)
So, the only difference that I see (at first look) is that MySQL on PRODUCTION is a little bit older.
I do not know whether this problem will be resolved if I upgrade PRODUCTION MySQL version.
But in any case, I would like to listen to your comments on the above.