0

I've tried analyzing the EXPLAIN but unsure if I'm missing some helpful index. Queries take 20-30 seconds across 14 million rows.

 CREATE TABLE `domains` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(255) NOT NULL,
  `last_seen_at` timestamp NULL DEFAULT '1980-01-01 00:00:00',
  `failures` int(9) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain_UNIQUE` (`domain`),
  KEY `last_seen_index` (`last_seen_at`),
  KEY `failures_index` (`failures` DESC),
) ENGINE=InnoDB AUTO_INCREMENT=15917835 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

my query:

SELECT 
                    domains.id,
                    domains.domain,
                    domains.last_seen_at,
                    domains.failures
                FROM domains 
                WHERE 
                    domains.failures < 10
                ORDER BY domains.last_seen_at ASC 
                LIMIT 1
                FOR UPDATE SKIP LOCKED

explain:

+----+-------------+---------+------------+-------+------------------------------------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys                            | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+------------------------------------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | domains | NULL       | index | failures_index                           | last_seen_index | 5       | NULL |    2 |    50.00 | Using where |
+----+-------------+---------+------------+-------+------------------------------------------+-----------------+---------+------+------+----------+-------------+
mysql> SELECT VERSION();
+---------------+
| VERSION()     |
+---------------+
| 8.0.18-google |
+---------------+
4
  • What can you tell us about the expected data distribution - do you expect most rows to have failures < 10. Do you expect that the rows which have the smallest last_seen_at values to have failures < 10 ? Commented Nov 6, 2021 at 19:30
  • @AndrewSayer, a small % should have failures > 10, but no correlation between last_seen_at and failures. i have workers cycling through domains regularly.
    – d-_-b
    Commented Nov 6, 2021 at 19:50
  • So if you were to look at the rows in order with the smallest last_seen_at value first, you would expect to find a row which matches your failures predicate very soon? Commented Nov 6, 2021 at 19:59
  • Yes, that's correct.
    – d-_-b
    Commented Nov 6, 2021 at 20:18

1 Answer 1

1

The Optimizer is between a rock and a hard place.

Using last_seen_index avoids the need for a sort, but might have to scan the entire table without finding even 1 row.

If most of the rows have very few "failures", using failures_index might lead to scanning most of the table, do a big sort, and finally delivering one row.

Since you seem to be picking up the "oldest" record and are probably going to run the same query frequently, consider "remembering where you left off" and sticking that into the WHERE:

AND last_seen_at >= ?

Then, the Optimizer might correctly pick last_seen_index as the better index. And it would not have to scan the entire table.

Think of writing the query as the next iteration of scanning through a large table based on the timestamp.

I find the "remember where you left off" a common optimization; see big deleted, pagination, summary tables, and big alters in http://mysql.rjweb.org/

I am assuming that last_seen_at is updated whenever failures in incremented? If no, can you add a last_checked timestamp?

Run out of data

Eventually the job will find nothing to do. That is, it will do the select and find that it is at the "end" of the table (based on last_seen_at). At that point, it should start over with the oldest possible date (~1970)`.

2
  • Thanks for that explanation. last_seen_at is incremented regardless of success/failure. Unfortunately, the current schema design inserts new, never-before-seen items in with a 1970 last_seen_at, so i have no way to keep track of where i left off.
    – d-_-b
    Commented Nov 8, 2021 at 15:29
  • @d-_-b - No problem -- see what I added to my Answer.
    – Rick James
    Commented Nov 8, 2021 at 18:30

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.