Explain doesn't show indexed column as key Consider the following table structure

CREATE TABLE `games` (
 `game_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `players_no` tinyint(4) DEFAULT NULL,
 `startedon` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `game_type` enum('R','C') NOT NULL DEFAULT 'R',
 `expirydatetime` datetime NOT NULL,
 `users_info` text NOT NULL,     
 `lastupdate` datetime NOT NULL,
 `count_temp_user` int(1) NOT NULL DEFAULT '0',
 `msg_pid` varchar(3) NOT NULL,
 PRIMARY KEY (`game_id`),
 KEY `startedon` (`startedon`),
) ENGINE=InnoDB AUTO_INCREMENT=11009966 DEFAULT CHARSET=utf8


Now,Consider the following query 

SELECT * FROM `games` WHERE DATE(games.expirydatetime)&lt;CURDATE() AND `players_no` = 2


When i Explain the above query i have the following result

id|select_type|table|type|possible_keys|key |key_len|ref|rows|filtered|Extra
 1 |SIMPLE     |games|ALL |NULL         |NULL|NULL|NULL|314|100.00|Using where

If i add an index on expirydatetime,i still don't see expirydate in key column after explain.Any Explainations for this behavior?Is it because expirydatetime is used within a mysql in-built function?

Will mysql still consider the index on expirydatetime?
First, the possible_keys shows null because there is no index on any of the two columns used in the WHERE clause. So, there is no useful index to even be considered for this query.

But even when you add an index on (expirydatetime), the situation is still not good. Because when you place a column inside a function in a condition, like the DATE(games.expirydatetime)&lt;CURDATE(), you are basically forbidding the use of indexes. While it might be possible to use the index on the column, it would still had to do a full index scan (because of the function use) and then access the table for the rows that matched. So the optimizer chooses to do a table scan instead.



To make the query use indexes and be more efficient, change the condition to:

WHERE expirydatetime &lt; CURDATE() 
  AND players_no = 2


and add a composite index on (players_no, expirydatetime)