Consider a table created like this:
CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT NULL, `reviewed` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `owner_id reviewed` (`owner_id`,`reviewed`), KEY `device_id reviewed` (`device_id`,`reviewed`) );
This table records events that happen to a device which has an owner. There are ~1000 owners, each of which can have 1-100 devices. Each device can have millions of events in the table.
We need two queries to tell us:
- Does a given
owner_idhave unreviewed events on any device?
- Does a given
device_idhave unreviewed events?
We have been using:
SELECT 1 FROM someTable s WHERE s.reviewed = 0 AND s.owner_id = 0 LIMIT 1;
SELECT 1 FROM someTable s WHERE s.reviewed = 0 AND s.device_id = 0 LIMIT 1;
But we have found that as the number of events grows these queries are slowing down unexpectedly. Running EXPLAIN on them says that we will examine many rows. I admit that I was expecting that it would only need to examine 1 row. My thinking was that MySQL could look up the corresponding index (
'owner_id reviewed' index or '
device_id reviewed' index) and tell immediately whether or not there were any corresponding rows.
I understand that EXPLAIN's
rows column is just an estimate and that it may not be entirely accurate for queries with
LIMIT 1, but I have found that the runtimes of these queries is increasing into many seconds (sometimes minutes) as the data set increases, which leads me to believe that MySQL is indeed examining more than one row.
What am I doing wrong here? How can I restructure my queries (or indexes) to do this efficiently?
We are running MySQL 5.6.21 on Linux. The table is innodb. I have created a simple SQL fiddle here: http://sqlfiddle.com/#!9/f0f4f1/1