How to Retrieve Table Row Count in MySQL 8.0 – Methods, Deprecation of sql_calc_found_rows and Performance Comparison

MySQL historically struggled with efficiently executing COUNT(*) because InnoDB must compute the count at query time, unlike MyISAM which kept a built‑in counter.

The article presents five techniques to obtain a row count for a table named ytt1:

1. Simulate MyISAM counter – create a small auxiliary table ytt1_count and maintain it with INSERT/DELETE triggers. Querying this table returns the count instantly, but the extra triggers add write overhead.

CREATE TABLE ytt1_count (cnt BIGINT);
-- trigger on INSERT to ytt1
INSERT INTO ytt1_count VALUES (NEW.id);
-- trigger on DELETE to ytt1
DELETE FROM ytt1_count WHERE id = OLD.id;
SELECT cnt FROM ytt1_count;

2. Use the deprecated SQL_CALC_FOUND_ROWS feature – run a SELECT with the flag and then call FOUND_ROWS(). This method performs a full table scan and is unreliable in replication environments; MySQL 8.0 issues a warning that it will be removed.

SELECT SQL_CALC_FOUND_ROWS * FROM ytt1 WHERE 1 LIMIT 1;
SELECT FOUND_ROWS() AS 'count(*)';

3. Read approximate value from the data dictionary – query information_schema.tables for TABLE_ROWS. The result is an estimate, suitable for displaying large‑scale news feeds where exactness is not critical.

SELECT table_rows FROM information_schema.tables WHERE table_name = 'ytt1';

4. Derive count from primary‑key continuity – if the primary key is dense and gap‑free, the maximum id equals the row count. SELECT MAX(id) AS cnt FROM ytt1; 5. Standard recommended method (MySQL 8.0.17) – simply execute SELECT COUNT(*) FROM ytt1. This is the preferred approach after the deprecation of SQL_CALC_FOUND_ROWS. SELECT COUNT(*) FROM ytt1; The article then compares the execution cost of COUNT(*) on MySQL 5.7 versus MySQL 8.0 using EXPLAIN FORMAT=JSON. In MySQL 5.7 the query cost is about 622.40, while in MySQL 8.0 it drops to roughly 309.95, showing a roughly two‑fold improvement.

Conclusion: the SQL_CALC_FOUND_ROWS syntax is deprecated and should be avoided; the straightforward COUNT(*) query is now efficient enough for most scenarios.