Explain doesn't show indexed column as key
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)