Why isn't MySQL using any of these possible keys?

Question

I have the following query:

SELECT t.id FROM account_transaction t JOIN transaction_code tc ON t.transaction_code_id = tc.id JOIN account a ON t.account_number = a.account_number GROUP BY tc.id 

When I do an EXPLAIN the first row shows, among other things, this:

table: t type: ALL possible_keys: account_id,transaction_code_id,account_transaction_transaction_code_id,account_transaction_account_number key: NULL rows: 465663 

Why is key NULL?

Answer 1

Another issue you may be encountering is a data type mis-match. For example, if your column is a string data type (CHAR, for ex), and your query is not quoting a number, then MySQL won't use the index.

SELECT * FROM tbl WHERE col = 12345; # No index SELECT * FROM tbl WHERE col = '12345'; # Index 

Source: Just fought this same issue today, and learned the hard way on MySQL 5.1. :)

Edit: Additional information to verify this:

mysql> desc das_table \G *************************** 1. row ***************************   Field: das_column    Type: varchar(32)    Null: NO     Key: PRI Default:    Extra:  *************************** 2. row *************************** [SNIP!]  mysql> explain select * from das_table where das_column = 189017 \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: das_column          type: ALL possible_keys: PRIMARY           key: NULL       key_len: NULL           ref: NULL          rows: 874282         Extra: Using where 1 row in set (0.00 sec)  mysql> explain select * from das_table where das_column = '189017' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: das_column          type: const possible_keys: PRIMARY           key: PRIMARY       key_len: 34           ref: const          rows: 1         Extra:  1 row in set (0.00 sec)