How to optimize this query or these many indexes?

Question

What am I doing wrong here? I will post the table structure and indexes first, then the query that is slow, and many others that are very fast (instant).

How to optimize that one query to run faster ?

I know I must have added too many individual indexes, should I group them in this case ? If so, how ?

The table has 40 million records, a bit hard/slow to experiment with different index settings without knowing what I am doing.

Table and indexes:

DESCRIBE domains;
+------------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+------------------------------+--------------+------+-----+---------+-------+
| domain_name                  | varchar(128) | NO   | PRI |         |       |
| domain_premium_extension     | tinyint(1)   | YES  | MUL | NULL    |       |
| domain_contains_letters      | tinyint(1)   | NO   | MUL | 0       |       |
| domain_contains_numbers      | tinyint(1)   | NO   |     | 0       |       |
| domain_contains_dashes       | tinyint(1)   | NO   |     | 0       |       |
| domain_chars                 | tinyint(1)   | NO   | MUL | 0       |       |
| domain_rank                  | mediumint(9) | NO   | MUL | NULL    |       |
| domain_rank_delta            | mediumint(9) | NO   |     | 0       |       |
| domain_rank_date             | int(11)      | NO   | MUL | NULL    |       |
| domain_best_rank             | mediumint(9) | NO   |     | NULL    |       |
| domain_worst_rank            | mediumint(9) | NO   |     | NULL    |       |
| domain_whois_status          | varchar(32)  | YES  |     | NULL    |       |
| domain_whois_email           | varchar(128) | YES  |     | NULL    |       |
| domain_whois_expiration_date | int(11)      | NO   | MUL | NULL    |       |
| domain_whois_date            | int(11)      | NO   | MUL | NULL    |       |
| domain_ip_date               | int(11)      | NO   | MUL | NULL    |       |
| domain_availability          | tinyint(1)   | YES  |     | NULL    |       |
| domain_availability_date     | int(11)      | NO   | MUL | NULL    |       |
| domain_virustotal_date       | bigint(20)   | NO   | MUL | 0       |       |
| domain_misc                  | tinyint(4)   | YES  | MUL | NULL    |       |
+------------------------------+--------------+------+-----+---------+-------+
20 rows in set (0.000 sec)


SHOW index from domains;
+---------+------------+------------------------------+--------------+------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                     | Seq_in_index | Column_name                  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------------------+--------------+------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| domains |          0 | PRIMARY                      |            1 | domain_name                  | A         |    43440162 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_rank_date             |            1 | domain_rank_date             | A         |    10860040 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_rank                  |            1 | domain_rank                  | A         |     2068579 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_whois_date            |            1 | domain_whois_date            | A         |    21720081 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_whois_expiration_date |            1 | domain_whois_expiration_date | A         |    21720081 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_ip_date               |            1 | domain_ip_date               | A         |    43440162 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_availability_date     |            1 | domain_availability_date     | A         |     3949105 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_virustotal_date       |            1 | domain_virustotal_date       | A         |    43440162 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_misc                  |            1 | domain_misc                  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| domains |          1 | domain_contains_letters      |            1 | domain_contains_letters      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_contains_letters      |            2 | domain_contains_numbers      | A         |        1038 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_contains_letters      |            3 | domain_contains_dashes       | A         |        3114 |     NULL | NULL   |      | BTREE      |         |               |
| domains |          1 | domain_premium_extension     |            1 | domain_premium_extension     | A         |        1092 |     NULL | NULL   | YES  | BTREE      |         |               |
| domains |          1 | domain_chars                 |            1 | domain_chars                 | A         |       23621 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+------------------------------+--------------+------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

This is the query that runs too slow, over a minute to run:

EXPLAIN SELECT * FROM domains WHERE domain_chars <= 4  AND domain_whois_expiration_date < 1689903739 AND domain_premium_extension = 1 ORDER BY domain_availability_date ASC LIMIT 50;
+------+-------------+---------+-------+--------------------------------------------------------------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys                                                      | key                      | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+--------------------------------------------------------------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | domains | index | domain_whois_expiration_date,domain_premium_extension,domain_chars | domain_availability_date | 4       | NULL |  100 | Using where |
+------+-------------+---------+-------+--------------------------------------------------------------------+--------------------------+---------+------+------+-------------+

All these below queries run instantly:

EXPLAIN SELECT * FROM domains ORDER BY domain_availability_date ASC LIMIT 50;
+------+-------------+---------+-------+---------------+--------------------------+---------+------+------+-------+
| id   | select_type | table   | type  | possible_keys | key                      | key_len | ref  | rows | Extra |
+------+-------------+---------+-------+---------------+--------------------------+---------+------+------+-------+
|    1 | SIMPLE      | domains | index | NULL          | domain_availability_date | 4       | NULL |   50 |       |
+------+-------------+---------+-------+---------------+--------------------------+---------+------+------+-------+


EXPLAIN SELECT * FROM domains WHERE domain_chars <= 4 ORDER BY domain_availability_date ASC LIMIT 50;
+------+-------------+---------+-------+---------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys | key                      | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+---------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | domains | index | domain_chars  | domain_availability_date | 4       | NULL |  567 | Using where |
+------+-------------+---------+-------+---------------+--------------------------+---------+------+------+-------------+


EXPLAIN SELECT * FROM domains WHERE domain_chars <= 4  AND domain_whois_expiration_date < 1689903739 ORDER BY domain_availability_date ASC LIMIT 50;
+------+-------------+---------+-------+-------------------------------------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys                             | key                      | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+-------------------------------------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | domains | index | domain_whois_expiration_date,domain_chars | domain_availability_date | 4       | NULL |  567 | Using where |
+------+-------------+---------+-------+-------------------------------------------+--------------------------+---------+------+------+-------------+


EXPLAIN SELECT * FROM domains WHERE domain_premium_extension = 1 ORDER BY domain_availability_date ASC LIMIT 50;
+------+-------------+---------+-------+--------------------------+--------------------------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys            | key                      | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+--------------------------+--------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | domains | index | domain_premium_extension | domain_availability_date | 4       | NULL |  100 | Using where |
+------+-------------+---------+-------+--------------------------+--------------------------+---------+------+------+-------------+


EXPLAIN SELECT * FROM domains WHERE domain_chars <= 4  AND domain_whois_expiration_date < 1689903739 AND domain_premium_extension = 1 LIMIT 500;
+------+-------------+---------+------+--------------------------------------------------------------------+--------------------------+---------+-------+----------+-------------+
| id   | select_type | table   | type | possible_keys                                                      | key                      | key_len | ref   | rows     | Extra       |
+------+-------------+---------+------+--------------------------------------------------------------------+--------------------------+---------+-------+----------+-------------+
|    1 | SIMPLE      | domains | ref  | domain_whois_expiration_date,domain_premium_extension,domain_chars | domain_premium_extension | 2       | const | 21720081 | Using where |
+------+-------------+---------+------+--------------------------------------------------------------------+--------------------------+---------+-------+----------+-------------+

Answer 1

Too Many Indexes

Fourteen indexes is probably too many indexes on a single table, especially since they're all single column indexes. Many of them are probably not even used and this is inefficient any time data is changed in your table too, as all of these indexes will need to be maintained in addition to the table itself. You'd be better off reducing the number indexes to a few specifically crafted composite indexes. I like to loosely follow a general rule of thumb of no more than 5 indexes per table, and no more than 5 columns per index, aka the 5 by 5 rule.

Use Composite Indexes On The Proper Fields

It's generally sensible to first index on the fields of the predicates (JOIN, WHERE, and HAVING clauses) of your query. These are the fields that reduce your data set. Then indexing by any ORDER BY clauses can be helpful too, by eliminating the need for an additional sort operation. Finally, including any of the columns needed in the SELECT clause (within reason) is helpful as well, to minimize the need to pull them from the primary index and reduce additional overhead - but more on that later.

Following the logic above, here is an example composite index that would make sense for your slow query: (domain_premium_extension, domain_chars, domain_whois_expiration_date, domain_availability_date). It is inclusive of the fields in your WHERE clause, and then the ORDER BY clause.

Design Indexes To Be Covering

Now another thing to be aware of with indexes is that the order of the fields specified in the definition affects what queries that index can cover. This is because the order of fields specified is also the logical order of which the data is sorted on when persisted in the index. In other words, an index defined on (ColumnA, ColumnB, ColumnC) is sorted on ColumnA first, then ColumnB next, and finally by ColumnC last.

What this also means is the index can be efficiently seeked on for any query whose predicates contain any subset combination of those fields, from left to right in the index definition. For example, a query that has a WHERE clause on all 3 columns will be applicable for that index. Additionally a query with a WHERE clause that references only ColumnA and ColumnB would also be applicable, same as well if it only referenced ColumnA. But a query with a WHERE clause that only referenced ColumnC or only referenced ColumnB and ColumnC would not be applicable, since those columns are defined after ColumnA in the index definition, meaning they are sorted after sorting the data on ColumnA. It's not possible to seek through ColumnA for only ColumnB and ColumnC, rather ColumnA would need to be scanned entirely first before being able to access ColumnB and then ColumnC.

So let's re-write the index I originally suggested bearing in mind the predicates of your other queries, to make it more covering. By re-ordering the same fields in the index definition to (domain_chars, domain_premium_extension, domain_whois_expiration_date, domain_availability_date) this index now covers and is efficiently applicable to your slow query, and the 2nd, 3rd, and 5th query in your example fast queries - all with a single index.

Don't Use SELECT *, It's An Anti-Pattern

You should almost never use SELECT * as it is an anti-pattern, hurts readability, affects maintainability, and can negatively affect performance for a multitude of reasons. You should explicitly list out only the columns you actually need back from that query. Because your queries currently return every column, it would not be reasonably possible (without severely breaking the 5 by 5 rule) to include the selected columns in an index.

This StackOverflow answer goes into some of the reasons why it is an anti-pattern and hurts performance. And this SoftwareEngineering.StackExchange question has a bunch of answers that discuss how it also hurts you from a maintainability perspective.

Answer 2

Because of multiple range tests (in that slow query), a "fast" index is not possible.

Give the Optimizer these; it will pick which is best based on the data:

INDEX(premium_extension, chars)
INDEX(premium_extension, whois_expiration_date)
INDEX(premium_extension, availability_date)

When adding those, DROP domain_premium_extension since each will suffice if you search by just premium_extension.

More: Index Cookbook

Notice how your "fast" cases each used domain_availability_date. They were lucky. What if the 50 rows desired had been at the end of that index? Those "fast" queries would be quite slow.

[I left off the common prefix domain_ since it is just clutter.]