Limiting your result set, but getting count of all rows

In reviewing some of my notes I thought it was relevant to ensure this small MySQL gem is documented for my history purposes.

Historically I’ve seen people do a complex SELECT statement twice, first to get a COUNT(*) and then to retrieve the data, perhaps in a paginated format. In MySQL you can combined both SELECT statements into 1 SQL. You still need a second SQL statement, but it is less impactful to your system.

Here is how?

mysql> select sql_calc_found_rows * from mysql.user limit 2;
...
2 rows in set (0.01 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)