Mysql 2.5M rows slow counting

Question

Having a simple mysql table with id(primary key) and hash(index). Some other columns (varchar / int), but queries for them are not needed.

My total table size is about 350 MB with 2.5 M rows.

SELECT COUNT(*) FROM table LIMIT 1;

takes about 0.5-1 s. My innodb buffer is set to 1 GB. I also tried options (no improvement), for example:

SELECT COUNT(id) FROM table LIMIT 1;

SELECT COUNT(*) FROM table WHERE id > 0 LIMIT 1;

One

SELECT * FROM table WHERE id = 'x' LIMIT 1;

will return within 1 ms (localhost mysql). Any tips for improving slow counting (0.5 - 1 s) would be very helpful.

Answer 1

, MyISAM . MyISAM. , , MyISAM.

COUNT(1) COUNT(*)

. . counter_table :

DELIMITER //

CREATE TRIGGER update_counter AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    UPDATE counter_table
    SET counter = counter + 1
END;

Answer 2

. , innodb , ( where, ). . .

, LIMIT 1 . group by, .