CODEWITHSUNDEEP
mysqlsqldatabaseoptimizationindexing
tmountain
tmountain

Reputation: 179

MySQL Refusing to Use Index for Simple Query

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.

Upvotes: 0

Views: 184

Answers (1)

tmountain
tmountain

Reputation: 179

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.

Upvotes: 1

Related Questions