Am I missing an index for these slow queries?

Question

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 |
+---------------+

Answer 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)`.