Mysql count performance on very big tables

Question

I have a table with more than 100 millions rows in Innodb. I have to know if there is more than 5000 rows where the foreign key = 1. I don't need the exact number. I made some testing : `SELECT COUNT(*) FROM table WHERE fk = 1` => 16 seconds `SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000` => 16 seconds `SELECT primary FROM table WHERE fk = 1` => 0.6 seconds I will have a bigger network and treatment time but it can be an overload of 15.4 seconds ! Do you have a better idea ? Thanks Edit: [Added OP's relevant comments] I tried SELECT SQL_NO_CACHE COUNT(fk) FROM table WHERE fk = 1 but it took 25 seconds Mysql was tuned for Innodb with Mysql Tuner. ``` CREATE TABLE table ( pk bigint(20) NOT NULL AUTO_INCREMENT, fk tinyint(3) unsigned DEFAULT '0', PRIMARY KEY (pk), KEY idx_fk (fk) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=100380914 DEFAULT CHARSET=latin1 ``` DB Stuff: ``` 'have_innodb', 'YES' 'ignore_builtin_innodb', 'OFF' 'innodb_adaptive_hash_index', 'ON' 'innodb_additional_mem_po…

Accepted Answer

Finally the fastest was to query the first X rows using C# and counting the rows number.

My application is treating the data in batches. The amount of time between two batches are depending the number of rows who need to be treated

```
SELECT pk FROM table WHERE fk = 1 LIMIT X
```

I got the result in 0.9 seconds.

Thanks all for your ideas!

Mysql count performance on very big tables

Solution

Problem