0

I have following readings table, it has around 500 million rows.

CREATE TABLE readings (
   id int(11) NOT NULL AUTO_INCREMENT,
   value double NOT NULL,
   created_dt datetime NOT NULL,
   device_id int(11) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),
   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),
   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)
) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt 
FROM readings 
WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00' 
  AND (device_id) IN (10, 11, 12));

Above query takes around 2 minutes.

DESCRIBE query returns the index used is readings_device_id_6a03c4ab761154d1_fk_device_id which is foreign key index on device_id. However if i remove the above index and run the query again, it uses readings_created_dt_483d31a3654ede43_uniq which is an index on both (created_dt, device_id) and retuns data in less than a second.

10
  • If you append created_dt at the end of readings_device_id_6a03c4ab761154d1_fk_device_id is this quick? What potion of the table does your date range represent? What proportion of the table do these 3 device_ids represent? What MySQL version?
    – danblack
    Mar 19, 2019 at 3:00
  • The optimizer is not infallible and can make mistakes. Force to use proper index. Or ignore unproper index. Index Hints.
    – Akina
    Mar 19, 2019 at 4:51
  • @danblack What MySQL version? (MySQL 5.7) What potion of the table does your date range represent? (Table contains last 3 years data.) What proportion of the table do these 3 device_ids represent? (Device table has around 10,000 devices.) If you append created_dt at the end of readings_device_id_6a03c4ab761154d1_fk_device_id is this quick? (Can you elaborate on this.)
    – Andro
    Mar 19, 2019 at 5:03
  • Appending to index, is really replacing, so: ALTER TABLE readings DROP INDEX readings_device_id_6a03c4ab761154d1_fk_device_id, ADD INDEX readings_device_id_6a03c4ab761154d1_fk_device_id ( device_id, created_dt). This means if it chooses this index because 3/10K devices is selective, it can use the same index to filter by device range. I'm not sure if using a device_id BETWEEN 10 AND 12 will give you a different query plan. Examine using EXPLAIN {query}
    – danblack
    Mar 19, 2019 at 5:07
  • 1
    If there is no need for id, then chuck it and have either (created_dt, device_id) or (device_id, created_dt) as the PK.
    – Rick James
    Mar 19, 2019 at 19:42

2 Answers 2

1

In my experience 5.6/5.7 is notorious for not spotting the correct index to choose for (range, range) type queries on large tables. You could try an analyze readings to see if it improves matters but I doubt it.

I have seen many examples of a SELECT on a large table filtered with a range drawn from a small list of possible values (your IN (1, 2, 3)) and an additional filter from small range drawn a massive spread of values (your created_dt BETWEEN) ... and at some threshold the planner will always choose to filter on the small list, leading to the results you observe.

The only way I have ever been able to solve this problem is either force the index if I can alter the query, or archive a significant (think 30-40%) chunk of the historical data to have the planner once again begin to choose the correct index.

Mysql 8 might be better, but I haven't any experience with really large tables on it yet.

0

In MySQL a range query on a column will always end the usage of it within a composite index, regardless if there are any usable columns following this column. Both the IN and BETWEEN are considered a range scan, so both would end up in the optimizer favoring the one with the (estimated) least number of rows returned. The statistics for a secondary index are the (estimated) number of rows covered by that index.

Your composite index on created_dt an device_id is defined like this:

UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),

The column created_dt is, I assume, almost entirely unique already, so the statistics think there is a high cardinality (uniqueness) in here.

While the device_id's index is defined as:

KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id)

I would assume there is a chance a device has many rows in this, which makes it easier to estimate the number of rows returned.

The optimizer does take a couple of things into consideration but the most important ones are the number of rows estimated and the number of traversals within the index. The only thing that helps is to force the index to the correct index (e.g. index hints)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.