Here is a little trick I found awhile back. I faced a challenge several months ago where I had to query a large, complex data result from MySQL. I “paged” through the results using LIMIT and OFFSET. However, I wanted to know the total number of rows w/o the LIMIT. The previous coder accomplished this by a query before appending the LIMIT statement at the end. This turned out to be very inefficient due to MySQL put together the 10000+ rows and sent it to PHP ready to be parsed, only to have the function mysql_num_rows() to be executed on it.
Instead I found on the internet (and I lost the link to the article) the MySQL trick “SQL_CALC_FOUND_ROWS.” I believe MySQL 4 and 5 support it. It allows you to place “SQL_CALC_FOUND_ROWS” right after the SELECT statement. Example:
[mysql] SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0, 10 [/mysql]
This tells MySQL to store a total number of rows that would have been returned regardless of the LIMIT statement. You retrieve this result but calling this query immediately after:
[mysql] SELECT FOUND_ROWS() AS `found_rows`; [/mysql]
So, in PHP you could do the following code:
[php] // ... $sql = "SELECT SQL_CAL_FOUND_ROWS * FROM users LIMIT 0, 10"; $result = mysql_query($sql); $sql = "SELECT FOUND_ROWS() AS `found_rows`;"; $rows = mysql_query($sql); $rows = mysql_fetch_assoc($rows); $total_rows = $rows['found_rows']; // ... [/php]
There is also a trick that if you want to insert SQL_CALC_FOUND_ROWS into the statement by the following PHP code:
[php] // ... $sql = trim($sql); $exploded = explode(" ", $sql); $exploded .= " SQL_CALC_FOUND_ROWS "; $sql = implode(" ", $exploded); // ... [/php]
Hopefully this can help someone looking to optimize their queries while not loosing functionality.