Why doesn't MySQL use 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 EXPLAIN, the first line shows, among other things, the following:

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 the key NULL?

Answer 1

Perhaps this is due to the fact that the statistics are broken or because it knows that you always have a 1: 1 ratio between two tables.

You can force the index to be used in the query and see if this action speeds up. If so, try running ANALYZE TABLE to make sure the statistics are updated.

USE INDEX (index_list), MySQL . IGNORE INDEX (index_list) , MySQL - . , EXPLAIN , MySQL .

FORCE INDEX, USE INDEX (index_list), , . , , .

, . . , SHOW INDEX.

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Answer 2

, , . , (CHAR, ex), , MySQL .

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

: MySQL 5.1.:)

: :

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)

Answer 3

group by (= order by)

...
GROUP BY tc.id

tc.id.
tc.id .
t.transaction_id -.

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 t.transaction_code_id

transaction_code_id.

() , , MySQL .

(40% IIRC) . MySQL . ( )