CODEWITHSUNDEEP
mysqlindexing
Abhi
Abhi

Reputation: 2013

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 -

  1. PRIMARY on ID
  2. playerLead_status_INDEX on status

Thanks in advance!

Upvotes: 4

Views: 13054

Answers (2)

uzumas
uzumas

Reputation: 678

Query planner calculates costs for several paths to execute a given query. The path with the least cost is selected. Using an index scan VS table scan are 2 paths. Let's consider both paths:

  1. Since you have an index on status and are trying to retrieve other columns also, your index is not a covering index. So when a key satisfies where clause, innodb needs to read the data for that row from disk (as index itself does not have it). 'status' field seemingly only has 2 values. For any of these 2 values, a lot of rows will qualify. For eg: if 1k rows are identified, this means 1k random reads from the disk
  2. Let's assume your table has 10k rows. If table scan is used, that would mean scanning and reading all the pages for the table. but these will mostly be sequential reads.

Thus, Case 1 can incur a lot more cost than case2. You should check the index cardinality to get an idea on this. Usually, indexes with very low cardinality and very low selectivity are bad choices i.e. unique values are very low(status) and actual # of rows is very high.

Upvotes: 0

Rahul
Rahul

Reputation: 77876

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?

Upvotes: 7

Related Questions