MySQL Forums
Forum List  »  Optimizer & Parser

Re: Why MySQL does not use an index?
Posted by: Panayotis Matsinopoulos
Date: February 10, 2012 01:21AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1056
January 19, 2012 12:25AM
Re: Why MySQL does not use an index?
1135
February 10, 2012 01:21AM
949
February 10, 2012 09:50AM
1067
February 11, 2012 10:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.