MySQL Forums
Forum List  »  Optimizer & Parser

Why MySQL does not use an index?
Posted by: Panayotis Matsinopoulos
Date: January 16, 2012 02:50AM

Hi,

I have a table which is described as follows:


+-----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| type | varchar(255) | NO | MUL | NULL | |
| data | text | YES | | NULL | |
| created_at | datetime | YES | MUL | NULL | |
| updated_at | datetime | YES | | NULL | |
| assessment_session_id | int(11) | NO | MUL | NULL | |
| device_ip | varchar(255) | YES | MUL | NULL | |
| device_id | varchar(255) | YES | MUL | NULL | |
| credit_card_hash | varchar(255) | YES | MUL | NULL | |
+-----------------------+--------------+------+-----+---------+----------------+

and its keys:

+--------+------------+-------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+
| events | 0 | PRIMARY | 1 | id | A | 3549801 | NULL | NULL | | BTREE | |
| events | 1 | events_assessment_sessions_fk | 1 | assessment_session_id | A | 3549801 | NULL | NULL | | BTREE | |
| events | 1 | events_type_idx | 1 | type | A | 16 | NULL | NULL | | BTREE | |
| events | 1 | events_device_ip_idx | 1 | device_ip | A | 1774900 | NULL | NULL | YES | BTREE | |
| events | 1 | events_credit_card_hash_idx | 1 | credit_card_hash | A | 591633 | NULL | NULL | YES | BTREE | |
| events | 1 | events_device_id_idx | 1 | device_id | A | 1774900 | NULL | NULL | YES | BTREE | |
| events | 1 | events_created_at_idx | 1 | created_at | A | 3549801 | NULL | NULL | YES | BTREE | |
+--------+------------+-------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+

As you can see, there is an index on the "created_at" column. However, when I run the following query:


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');

It does not use the 'events_created_at_idx' and query is slow. The explain command returns the following:

+----+-------------+--------+-------------+------------------------------------------------------------+--------------------------------------+---------+------+------+--------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | 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 | 1631 | Using intersect(events_device_ip_idx,events_type_idx); Using where |
+----+-------------+--------+-------------+------------------------------------------------------------+--------------------------------------+---------+------+------+--------------------------------------------------------------------+

I would like to know the reason why it does not use the index on the created_at column. If I use the "force index" predicate then it uses the index and the query runs much faster. Is there a way to optimize this query somehow without using the "force index"?

Thanks in advance



Edited 1 time(s). Last edit at 02/10/2012 02:49AM by Panayotis Matsinopoulos.

Options: ReplyQuote


Subject
Views
Written By
Posted
Why MySQL does not use an index?
2852
January 16, 2012 02:50AM
1262
January 19, 2012 12:25AM
1159
February 10, 2012 09:50AM
1269
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.