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?

You should paste entire EXPLAIN output. By the looks of it, there's no limiting factor for table t, meaning your query takes all of the data from table t hence no need to use index whatsoever. — N.B., Apr 19 '11 at 17:42
perhaps your query returns all rows from account_transaction, in which case a key might not be useful. — nos, May 02 '11 at 19:22

Spikes · Answer 1 · 2011-05-02T19:20:59.517

72

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)

edited May 02 '11 at 19:20

answered Apr 29 '11 at 01:19

Spikes

1,036
1
6
8

Indeed it's true... updating comment with output from MySQL to verify. – Spikes May 02 '11 at 19:17
1

A bug was created with MySQL about this issue. Logging here in case anyone else involved in this thread was interested.[link](http://bugs.mysql.com/bug.php?id=61778) – Spikes Jul 06 '11 at 21:13
6

This happened to us today: mysql wasn't using an index to join because table1 was utf8 and table2 was latin1 – benhsu Jul 24 '12 at 15:14
Props to @benhsu ... ran into same problem today and your comment triggered my solution.... thanks! – Brian Adkins Sep 17 '12 at 21:55
I encountered the same problem as Spikes described, but when joining two columns of which one was a varchar and the other an int. – Ekster Apr 25 '13 at 12:36
This may seem stupid, but comparing strings and numbers are quite different. You can't just assume different data-type will produce the same B-Tree. It doesn't make sense. – Kirk Backus Oct 15 '13 at 22:13
@Spikes, Can't seem to replicate this anymore. Has the problem been fixed? – Pacerier Feb 01 '15 at 23:01
Great answer, that did it for me =) – Ted Jan 04 '16 at 12:05
Does this problem still exists today (5 years after you answer this question)? – JaskeyLam Jan 19 '16 at 11:41
This is the reason my index was not working in mysql version 5.7.18. I was searching an indexed varchar field with an integer value – OtotheA Aug 26 '18 at 20:24
This solved my problem, thanks. I was testing whether an integer column was 1 or 0 by using `is true` (something I've never done before and was just trying out, so I guess it's a good thing I'd never done it). – felwithe Apr 27 '20 at 19:25
In my case, the RCA is the same. (The columns which I am trying to join have a different data type.) – Manan Shah Jun 02 '20 at 09:30

score 22 · Accepted Answer · edited Jun 20 '20 at 09:12

It might be because the statistics is broken, or because it knows that you always have a 1:1 ratio between the two tables.

You can force an index to be used in the query, and see if that would speed up things. If it does, try to run ANALYZE TABLE to make sure statistics are up to date.

By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) can be used to tell MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

You can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is PRIMARY. To see the index names for a table, use SHOW INDEX.

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

Do not use force index on **simple** queries like this one. There is a reason MySQL is not using the index. It is making your query **faster** by not using the index. Don't slow it down by forcing the index. Anyway always time your selects. — Johan, Apr 19 '11 at 18:09
@Johan, But [there are cases](http://stackoverflow.com/a/5504594/632951) where the [guesses are severely bad](http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index). It's better to do it yourself unless what you are optimizing is development speed. — Pacerier, Feb 01 '15 at 23:31

score 9 · Answer 3 · answered Apr 19 '11 at 18:17

Index for the group by (=implicit order by)

...
GROUP BY tc.id

The group by does an implicit sort on tc.id.
tc.id is not listed a a possible key.
but t.transaction_id is.

Change the code to

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

This will put the potential index transaction_code_id into view.

Indexes for the joins
If the joins (nearly) fully join the three tables, there's no need to use the index, so MySQL doesn't.

Other reasons for not using an index
If a large % of the rows under consideration (40% IIRC) are filled with the same value. MySQL does not use an index. (because not using the index is faster)

1

IIRC: If I Recall Correctly – Johan Jan 15 '21 at 11:06

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

3 Answers3

Linked