What other steps to take to increase fetch speed in MySQL?

Question

MySQL

There is a big table in which about 3 million lines. All indexes are correct. But they no longer save, because a query like this:

SELECT a.id, a.title, a.body FROM articles a WHERE a.isDeleted=0 AND a.isReady=1 AND a.id IN (1,2,3,4,5,6,7)

after the server is restarted, it takes about a second.
Also, about 1000 new records are added to this table every day.
What can be done to increase the sampling rate?
Here is the actual query that is being executed:

SELECT   a.ID  FROM articles a WHERE a.ID IN       (244420, 244568, 290772, 440668, 483669, 515012, 536432, 581513, 1096597, 1465361, 1801110, 1950167, 2101815, 2337877, 2960983, 2963083, 2968763, 2971218, 2971692, 2976138, 2978343, 2979835, 2982769, 2985508, 2997775, 3005673, 3009886, 3010798, 3013842, 3014869, 3021023, 3025274, 3028399, 3029632, 3037355, 3040442, 3051786, 3053527, 3059715, 3065847, 3072606, 3088423, 3095432, 3096762, 3119841, 3121399, 3131721, 3149442, 3160328)       AND Ready = TRUE AND IsDeleted = FALSE ORDER BY a.ID DESC LIMIT 42;

EXPLAIN:

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   49 |     1.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Answer 1

The query fragment "a.ID IN (nnn,nn)" already selects by the fastest method by the primary key all the necessary information, which is further filtered.
Then the question is - how many columns do you get with the query, are there any extra ones?
Are there columns of TEXT, Varchar type, which have variable length, which can slightly reduce performance in some operations.
The next question is whether it's time to optimize the server settings - cache, memory. From mysqltuner.com to thoughtful analysis of mana and usage statistics.
Naive question - Do you reboot the server so often that the first second in the first request is so critical?
After the first access to the table, the index (partially?) is loaded into RAM and until it is forced out by someone else - it speeds up the query process very well. This has an interesting consequence - many indexes can even be harmful - they are not used in the query, but take up space on the hard disk and in RAM.
And for tests without caching, you don’t need to reboot the server. Use:
SELECT SQL_NO_CACHE a.ID FROM articles a WHERE ...

Answer 2

The query uses only the primary key, most likely by ID. Try adding a composite key (ID, Ready, IsDeleted).

Answer 3

>After restarting the server, it takes about a second.
buffer pool is empty. there was something about fast warming up, Percona for sure. Partitioning can also help.

Answer 4

Attach a list of indexes.
Is the slow query log enabled? If yes, what's in it?
Base settings?