Muhammed Imran Hussain

LAMP/LEMP, MEAN, JAVA Developer

SQL: Faster way to COUNT records from large data set

For small data set COUNT() function works fine but for large data set for example 1M records the query may exceed time limit.

Since MySQL >= 4.0.0 we can use SQL_CALC_FOUND_ROWS option and FOUND_ROWS() function.

SQL_CALC_FOUND_ROWS tells MySQL to count total number of rows disregarding LIMIT clause. After SQL_CALC_FOUND_ROWS in second query we can use FOUND_ROWS() function to get total number of rows.

Queries would look like following:

For example.
Table Name: Orders
Column name: id, amount, …

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.