MySQL Refusing to Use Index for Simple Query

Question

I have a table that I'm running a very simple query against. I've added an index to the table on a high cardinality column, so MySQL should be able to narrow the result almost instantly, but it's doing a full table scan every time. Why isn't MySQL using my index?

mysql> select count(*) FROM eventHistory;
+----------+
| count(*) |
+----------+
|   247514 |
+----------+
1 row in set (0.15 sec)

CREATE TABLE `eventHistory` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `whatID` varchar(255) DEFAULT NULL,
    `whatType` varchar(255) DEFAULT NULL,
    `whoID` varchar(255) DEFAULT NULL,
    `createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `whoID` (`whoID`,`whatID`)
) ENGINE=InnoDB;

mysql> explain SELECT * FROM eventHistory where whoID = 12551\G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: eventHistory
         type: ALL
possible_keys: whoID
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 254481
        Extra: Using where
1 row in set (0.00 sec)

I have tried adding FORCE INDEX to the query as well, and it still seems to be doing a full table scan. The performance of the query is also poor. It's currently taking about 0.65 seconds to find the appropriate row.

Try explain SELECT * FROM eventHistory where whoID = '12551'. Demo. — Glavić
– Glavić, Commented Oct 17, 2013 at 18:24
Both pieces of advice are extremely helpful. I converted my local copy of the table to use an integer for the column, and the query is now instant. Adding the quotes around the query criteria also yields the same result. Thanks very much! — tmountain
– tmountain, Commented Oct 17, 2013 at 18:38
To bad MySQL doesn't give an error when you mismatch an datatype.. but silent use an cast and destroy the index.. — Raymond Nijland
– Raymond Nijland, Commented Oct 17, 2013 at 18:47
try adding index only to whoID and let us know how it goes — jcho360
– jcho360, Commented Oct 17, 2013 at 19:16

tmountain · Accepted Answer · 2013-10-17 19:17:04Z

1

The above answers lead me to realize two things.

1) When using a VARCHAR index, the query criteria needs to be quoted or MySQL will refuse to use the index (implicitly casting behind the scenes?)

SELECT * FROM foo WHERE column = '123'; # do this
SELECT * FROM foo where column = 123; # don't do this

2) You're better off using/indexing an INT if at all possible.

answered Oct 17, 2013 at 19:17

tmountain

1791 gold badge1 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Refusing to Use Index for Simple Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related