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:
-
SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0, 10
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:
-
SELECT FOUND_ROWS() AS `found_rows`;
So, in PHP you could do the following code:
PHP:
// ... $sql = "SELECT SQL_CAL_FOUND_ROWS * FROM users LIMIT 0, 10"; $sql = "SELECT FOUND_ROWS() AS `found_rows`;"; $total_rows = $rows['found_rows']; // ...
There is also a trick that if you want to insert SQL_CALC_FOUND_ROWS into the statement by the following PHP code:
Hopefully this can help someone looking to optimize their queries while not loosing functionality.
Related Posts
- My 2009 Technology Recap Its been two years now that I've been a more or less "serious blogger." I had using the term blogger, since when people hear about blogs, they think of people either detailing their entire lives, or pumping some angel. For me, blogging has just been a way to share information...
- Presentation: Real Life Scaling Here are my slides for the presentation that I gave at the Utah Open Source Conference on Friday. It was an awesome conference, and I am glad that I was able to be involved. Real Life Scaling: A Tale of Two Websites View more presentations from Justin Carmony. If you...

0 Responses
Comments RSS Feed.