In order to show paging of our resources, we use LIMIT in our MySQL query. I met some scalability issues, while using the simplest way of getting total number of rows.
The simplest way to get total number of rows in you MySQL query is to run the following construction:
$query = mysql_query("SELECT COUNT(*) FROM table WHERE name LIKE 'a%'");
$result = mysql_fetch_array($query);
$total_num_rows = $result[0];
$query1 = mysql_query("SELECT * FROM table WHERE name LIKE 'a%' LIMIT 0,50");
/* HERE IS YOUR CODE PARCING THE RESULTS OF MAIN QUERY */
It works pretty good and fast if you have less than 100 results.
I tried this structure on a table with 1000 rows.
It takes the same time to return a page as if I ran query without LIMIT.
I found solution on this page MySQL: Get total number of rows when using LIMIT.
$query = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE name LIKE 'a%' LIMIT 0,50");
$query_count = mysql_query("SELECT FOUND_ROWS()");
$result = mysql_fetch_array($query_count);
$total_num_rows = $result[0];
/* HERE IS YOUR CODE PARCING THE RESULTS OF MAIN QUERY */
The same number of rows as it was in the first example, was returned in less than a second.