Mysql not using any index Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Mysql not using any index without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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?

Answer

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.

We are here to answer your question about Mysql not using any index - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji