CODEWITHSUNDEEP
mysql
Matt
Matt

Reputation: 1307

MySQL seems to ignore index on datetime column

I am having a pretty severe performance problem when comparing or manipulating two datetime columns in a MySQL table. I have a table tbl that's got around 10 million rows. It looks something like this:

`id` int(11) NOT NULL AUTO_INCREMENT,
`last_interaction` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
`last_maintenence` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
PRIMARY KEY (`id`),
KEY `last_maintenence_idx` (`last_maintenence`),
KEY `last_interaction_idx` (`last_interaction`),
ENGINE=InnoDB AUTO_INCREMENT=12389814 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

When I run this query, it takes about a minute to execute:

SELECT id FROM tbl
WHERE last_maintenence < last_interaction
ORDER BY last_maintenence ASC LIMIT 200;

Describing the query renders this:

          id: 1
  select_type: SIMPLE
        table: tbl
         type: index
possible_keys: NULL
          key: last_maintenence_idx
      key_len: 5
          ref: NULL
         rows: 200
        Extra: Using where

It looks like MySQL isn't finding/using the index on last_interaction. Does anybody know why that might be and how to trigger it?

Upvotes: 1

Views: 206

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Thats easy: Mistake No 1.

MySQL can only (mostly) use one index per query and the optimizer looks which one is better for using.

So, you must create a composite index with both fields.

Upvotes: 1

sanastasiadis
sanastasiadis

Reputation: 1202

The query you provide needs a full table scan. Each and every record has to be checked to match your criterion last_maintenence < last_interaction. So, no index is used when searching.

The index last_maintenence_idx is used only because you want the results ordered by it.

Upvotes: 1

e4c5
e4c5

Reputation: 53734

That is because mysql normally uses only one index per table. Unfortunately your query though it looks a simple one. The solution would be to create a composite index involving both columns.

Upvotes: 1

Related Questions