CODEWITHSUNDEEP
mysqlsqldatabaseperformancedatabase-performance
Floris
Floris

Reputation: 309

mysql 2.5M rows slow count

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

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

SELECT COUNT(*) FROM table LIMIT 1;

Is taking about 0.5 - 1s. My innodb buffer is set at 1GB. I've also tried variations (without improvements) like:

SELECT COUNT(id) FROM table LIMIT 1;

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

A single 

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

would return within 1 ms (localhost mysql). Any tips on improving the slow count (0.5 - 1s) would be greatly appreciated.

Upvotes: 4

Views: 281

Answers (2)

Alex Pliutau
Alex Pliutau

Reputation: 21937

Some time ago I have found for me, that MyISAM tables make these operations faster. But not all tables and architectures can be MyISAM. Check your schema, maybe you can switch this table to MyISAM.

Also use COUNT(1) instead of COUNT(*)

And another technique for you. Create trigger and save count in separated place. Create counter_table and folowing trigger:

DELIMITER //

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

Upvotes: 1

Tasos P.
Tasos P.

Reputation: 4124

You can find a bried explanation here. In short, innodb has to make a full table scan in order to count all rows (without a where clause, which would utilize an index). See also this answer.

BTW, I can't see any point in using LIMIT 1 in your query. Since there is no group by clause, it will always return one record.

Upvotes: 1

Related Questions