Mysql Index is not working

Can anyone please tell me why mysql is not using 'playerLead_status_INDEX' index?

CREATE TABLE `bcs_player_lead`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `email_id` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `status` int(11) DEFAULT NULL COMMENT '1-New,2-invitation send',
  PRIMARY KEY (`id`),
  KEY `playerLead_status_INDEX` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=510 DEFAULT CHARSET=latin1;

EXPLAIN SELECT email_id,id,NULL,first_name,last_name,NULL,state,city,NULL,(2) as type FROM bcs_player_lead WHERE status='1'

id|select_type|table |type|possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |bcs_player_lead |ALL |playerLead_status_INDEX|{null}|{null}|{null}| 458 |Using where

bcs_player_lead have two keys -

PRIMARY on ID

playerLead_status_INDEX on status

Thanks in advance!

---

I am no DBA but AFAIK, sometimes index is not used even if there is one: when table statistics are not updated (OR) probably less number of rows to retrieve as @Leo said (since in your case only 458 rows to retrieve).

You can force the use of index though in your query using FORCE INDEX (index_name) . Try it once and check the EXPLAIN PLAN

SELECT email_id,id,NULL,first_name,last_name,NULL,state,city,NULL,(2) as type 
FROM bcs_player_lead 
FORCE INDEX (playerLead_status_INDEX)
WHERE status='1'

You might want to check this post once MySQL not using indexes with WHERE IN clause?

链接地址: http://www.djcxy.com/p/25086.html

上一篇: 带JOIN的MySQL不使用索引

下一篇: Mysql索引不起作用