Mysql Index is not working

3
votes

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!

2
mysqlindexing
Not sure the version of MySQL you are using but considering you have Status with 'DEFAULT NULL' could be an issue. I found this on MySQL site dev.mysql.com/doc/refman/5.5/en/create-table.html "In MySQL 5.5, only the InnoDB, MyISAM, and MEMORY storage engines support indexes on columns that can have NULL values. In other cases, you must declare indexed columns as NOT NULL or an error results. ". The only thing I can come up with. – Jason McD
"...Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) ..." see dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Leo
That could be the case as @Leo said and the scenario is when table statistics is not updated. – Rahul
just checking, try adding LIMIT to your query (may be LIMIT 1000) => "...However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result. ..." – Leo
Abhi, using force index was just a check. In your case since it's fetching only 500 rows, even if it don't go for index seek you won't feel any slag. – Rahul

2 Answers

5
votes

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?

0
votes

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.