Description:
I know it has been discussed a lot that sql_calc_found_rows in MySQL is considered slower in many cases than running a second COUNT(*) query. But in my case SQL_CALC_FOUND_ROWS is considerably faster than using a 2nd COUNT(*).
In this case, using SQL_CALC_FOUND_ROWS with subsequent FOUND_ROWS() query is much faster than using COUNT(*) according to my tests.
Running COUNT(*) as a second query with no limit and offset clause and stripped from all the unnecessary JOINS, so that only the essential is left to get only the total count of rows found by my previous query, is at least 50 % slower.
On the other hand, SQL_CALC_FOUND_ROWS followed by FOUND_ROWS() is at least 50 % faster.
I'm sure I'm not the only one who sees better performance with SQL_CALC_FOUND_ROWS followed by FOUND_ROWS().
Considering all this, I would very much appreciate that mysql leaves the SQL_CALC_FOUND_ROWS as a legacy feature in their future releases of mysql. We and many others are using the SQL_CALC_FOUND_ROWS on their websites. For example, on our website, the query with SQL_CALC_FOUND_ROWS is the most important query and it is used by 10.000+ users on a daily basis and it performs 60-70 % faster than without SQL_CALC_FOUND_ROWS followed by COUNT(*).
How to repeat:
Create below tables and populate them with data and run the sql queries and test their execution times. For dummy data, you can download here the below two tables with dummy data already in them https://drive.google.com/drive/folders/1y0mSinHXTCyatDmR-P5ZjH_uDo4HTXXO?usp=sharing .
CREATE TABLE `savedprofiled` (
`UserId` INT(10) NOT NULL,
`attr_IAm` INT(10) UNSIGNED NULL DEFAULT NULL,
`attr_LookingFor` INT(10) UNSIGNED NULL DEFAULT NULL,
`attr_MemberOfWebsite` VARCHAR(255) NOT NULL DEFAULT '1001' COMMENT 'The denormalized attribute MemberOfWebsite (already escaped and ready for sql query)' COLLATE 'utf8mb3_general_ci',
PRIMARY KEY (`UserId`) USING BTREE,
INDEX `attr_IAm_attr_LookingFor_dSearchPriority_UserId` (`attr_IAm`, `attr_LookingFor`, `UserId`) USING BTREE,
INDEX `attr_IAm_attr_LookingFor_dSearchPriority_dActivityScore_UserId` (`attr_IAm`, `attr_LookingFor`, `UserId`) USING BTREE,
INDEX `attr_IAm_attr_LookingFor_dSearchPriority_dLikabilityScore_UserId` (`attr_IAm`, `attr_LookingFor`, `UserId`) USING BTREE,
FULLTEXT INDEX `attr_MemberOfWebsite` (`attr_MemberOfWebsite`)
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `users` (
`Id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(50) NOT NULL COLLATE 'utf8mb3_general_ci',
`WebsiteId` INT(10) UNSIGNED NOT NULL DEFAULT '1001',
`Banned` TINYINT(1) NOT NULL DEFAULT '0',
`Suspended` TINYINT(1) NOT NULL DEFAULT '0',
`Deleted` TINYINT(1) NOT NULL DEFAULT '0',
`PrivilegeId` INT(10) UNSIGNED NULL DEFAULT NULL,
`PriceTargetingId` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'When user registers on the site it updates to a price targeting Id. NULL if user has no price targeting assigned.',
PRIMARY KEY (`Id`) USING BTREE,
UNIQUE INDEX `Username` (`Username`, `WebsiteId`) USING BTREE,
UNIQUE INDEX `PrivilegeId` (`PrivilegeId`) USING BTREE,
INDEX `PriceStrategyId` (`PriceTargetingId`) USING BTREE,
INDEX `WebsiteId` (`WebsiteId`) USING BTREE,
CONSTRAINT `FK_users_price_targetings` FOREIGN KEY (`PriceTargetingId`) REFERENCES `price_targetings` (`Id`) ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT `FK_users_websites` FOREIGN KEY (`WebsiteId`) REFERENCES `websites` (`Id`) ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`PrivilegeId`) REFERENCES `orders_privileges` (`Id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=170204
;
-- Populate above tables with dummy data and run below tests.
-- test1 with SQL_CALC_FOUND_ROWS
SELECT SQL_CALC_FOUND_ROWS
tblUsers.Username AS Username
FROM savedprofiled tblSavedD
JOIN users tblUsers ON tblUsers.Id = tblSavedD.UserId
WHERE
tblSavedD.attr_IAm = 46015 AND tblSavedD.attr_LookingFor = 48010 AND
tblUsers.WebsiteId IN ('1000','1001') AND
MATCH(tblSavedD.attr_MemberOfWebsite) AGAINST ('+(1001)' IN BOOLEAN MODE) AND
tblSavedD.UserId <> '170036' AND
tblUsers.Suspended = 0 AND tblUsers.Deleted = 0 AND tblUsers.Banned = 0
ORDER BY tblSavedD.UserId DESC
LIMIT 0,10;
SELECT FOUND_ROWS();
-- Duration for 2 queries: 0,578 sec.
-- test2 without SQL_CALC_FOUND_ROWS
SELECT
tblUsers.Username AS Username
FROM savedprofiled tblSavedD
JOIN users tblUsers ON tblUsers.Id = tblSavedD.UserId
WHERE
tblSavedD.attr_IAm = 46015 AND tblSavedD.attr_LookingFor = 48010 AND
tblUsers.WebsiteId IN ('1000','1001') AND
MATCH(tblSavedD.attr_MemberOfWebsite) AGAINST ('+(1001)' IN BOOLEAN MODE) AND
tblSavedD.UserId <> '170036' AND
tblUsers.Suspended = 0 AND tblUsers.Deleted = 0 AND tblUsers.Banned = 0
ORDER BY tblSavedD.UserId DESC
LIMIT 0,10;
SELECT COUNT(*)
FROM savedprofiled tblSavedD
JOIN users tblUsers ON tblUsers.Id = tblSavedD.UserId
WHERE
tblSavedD.attr_IAm = 46015 AND tblSavedD.attr_LookingFor = 48010 AND
tblUsers.WebsiteId IN ('1000','1001') AND
MATCH(tblSavedD.attr_MemberOfWebsite) AGAINST ('+(1001)' IN BOOLEAN MODE) AND
tblSavedD.UserId <> '170036' AND
tblUsers.Suspended = 0 AND tblUsers.Deleted = 0 AND tblUsers.Banned = 0
-- Duration for 2 queries: 1,126 sec.
-- Conclusion: test1 (SQL_CALC_FOUND_ROWS followed by FOUND_ROWS()) is at least 50 % faster than test2 (no SQL_CALC_FOUND_ROWS followed by COUNT(*))
Suggested fix:
Please leave the SQL_CALC_FOUND_ROWS as a legacy feature in future releases of mysql.
© 2026, Oracle Corporation and/or its affiliates