Mysql not using any index

Question

I have a rather simple query that for some reason is not using any index:

CREATE TABLE `events_self` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` int(10) unsigned NOT NULL,
`username` varchar(16) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`event` enum('status_update','follow_hashtag','make_hashtag','avatar','placeholder_3') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`obj_id` varchar(140) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`inline` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (`id`,`date`),
KEY `obj_id` (`obj_id`),
KEY `user_date` (`username`,`date`),
KEY `event` (`event`),
KEY `x` (`event`,`inline`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `events_self` (`id`, `date`, `username`, `event`, `obj_id`, `inline`) VALUES (1, 1358359266, 'aaa', 'make_hashtag', '1', 'scene');

SELECT inline FROM events_self  WHERE obj_id = 1;

EXPLAIN SELECT inline FROM events_self  WHERE obj_id = 1;


+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | events_self | ALL  | obj_id        | NULL | NULL    | NULL | 1610702 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Server version: 5.3.2-MariaDB-beta-mariadb102~lenny-log (MariaDB - http://mariadb.com/)

Should I leave the table / query as is or it would be best to force the use of an index?

Which is the question here? I don't see any question mark in your content — Mihai Matei
– Mihai Matei, Commented Jan 17, 2013 at 11:32
It is probably because for your query, MySQL has considered that not using an index is the best way to proceed. Try and force it to use the index you think it should and compare the results. — fge
– fge, Commented Jan 17, 2013 at 11:33
Since you have one single entry in you table, mysql estimated that it will take less resources to do a table scan that an index scan for your query. Don't forget that the query execution plan is taking into account the number of rows in the table. — ioan
– ioan, Commented Jan 17, 2013 at 11:36
@MateiMihai oops, i forgot to include my question. I've added it now on the question. — Sathia Musso
– Sathia Musso, Commented Jan 17, 2013 at 11:36
@ioan on the production server it counts: ~1,426,959 rows and several thousands are added daily — Sathia Musso
– Sathia Musso, Commented Jan 17, 2013 at 11:37

Venu · Accepted Answer · 2013-01-17 11:38:40Z

3

try this

EXPLAIN SELECT inline FROM events_self  WHERE obj_id = "1";

Since you sent the integer, MySql will not consider the indexing as type is different.

answered Jan 17, 2013 at 11:38

Venu

7,2894 gold badges42 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sathia Musso Over a year ago

ouch, I've never seen such a thing. apparently it works correctly now. unfortunately obj_id must be a varchar(140). do you know where I can read about type issues with indexes on Mysql? thanks

Venu Over a year ago

you can modify your query to send the string instead of int.

Sathia Musso Over a year ago

indeed: WHERE obj_id = 985591 LIMIT 1 => 1 row in set (0.66 sec) WHERE obj_id = "985591" LIMIT 1 => 1 row in set (0.00 sec)

Collectives™ on Stack Overflow

Mysql not using any index

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related