How to speed up SELECT count query?

Question

MySQL

How to speed up SELECT count(*) FROM table WHERE any_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) query?
There are 700,000+ records in the table, there is also an index on any_id, but the query takes about 2-3 seconds.
mariadb 10+ database, InnoDB type table

Answer 1

The only way to noticeably speed up COUNT is not to use COUNT, there are alternatives for certain tasks, for example, if you need COUNT for pagination, then you can choose the number of pages more than necessary by n times + 1 and do pagination based on this.
Example:
In a table of 700,000 records, we need to display the tenth page with 10 records with pagination: we select not 10, but 41 records (do not forget about offset), we display 10, but due to the fact that 31 records are selected ahead, we know what else there are at least 4 pages to which links can be displayed.

Answer 2

предрасчитывать всё это при insert/update

Answer 3

попробуйте IN заменить на ANY.

Answer 4

Попробуйте

SELECT count(*) FROM table WHERE any_id BETWEEN 1 and 9

Answer 5

Таблица со счётчиками, обновлять либо триггерами, либо из приложения при вставке/удалении, либо по крону, если погрешность допустима.
Можно тип данных для any_id взять поменьше размером, чтоб ужать индекс, но много это не даст.