mysql> select SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10;
mysql> select FOUND_ROWS();
使用SQL_CALC_FOUND_ROWS能夠在查詢時為您事先准備好符合where條件的記錄數目，然後只要在隨後執行一句select FOUND_ROWS(); 就能獲得總記錄數。
這個方法有一個副作用，當使用了SQL_CALC_FOUND_ROWS以後，將不能使用查詢緩存、在特殊情況下反而或損失一些性能。
例如，一個文章表，所有文章做了主鍵ID，並做了CREATE_TIME DESC的索引。這樣在執行
SELECT * FROM ARTICLE ORDER BY ID DESC LIMIT 10 或者 CREATE_TIME DESC LIMIT 20 時，數據庫引擎可以完全根據索引返回最新文章而不會管你有多少符合的記錄，但用了SQL_CALC_FOUND_ROWS後引擎不得不掃描全表以確定全部記錄數。
但無論怎樣，這個 SQL_CALC_FOUND_ROWS 非常適合where字句異常復雜耗時的情況。在頻繁使用查詢的應用中，這個方法能夠帶來1/3的數據庫性能提升。
Never, never use SQL_CALC_FOUND_ROWS it’s just equally slow then “SELECT COUNT(*) FROM table”, but you have to do it on every page. The count(*) variant you can cache at last, so you don’t have to do it on every page.
（用SQL_CALC_FOUND_ROWS 每頁每次查詢都會做Count；用 count(*)，則可以自己緩存結果）
And there’s even another way to avoid the count on paging. It’s the way Facebook does paging on some places. Facebook don’t give you the usually list of pages from 1 to n there, were you can click at any page. They just give you the page before, the current page and the next page, if there’s one. On the application side it’s very easy to find out if you have a page before the current, when you are on the second page there’s one before (so no surprise here). But what about the next page if you don’t want to make a count. Easy stuff, let me predict you display 10 items per page, so query 11 items instead of 10 per page. This one extra item will cost you nearly nothing and now you can count the returned rows in your application. If you have more than ten rows you have a next page and you can happily throw number eleven away.
（只顯示上下頁；程序很容易知道有沒有上一頁；判斷下一頁，只需要多查詢1條，則可以判斷有沒有下一條記錄了。）
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_wti_like_post on wp_wti_like_post.post_id=wp_posts.ID WHERE 1=1 AND ( ( post_date_gmt > ’2013-11-08 15:37:48′ ) ) AND ( wp_term_relationships.term_taxonomy_id IN (5) ) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘private’) GROUP BY wp_posts.ID ORDER BY wp_wti_like_post.value DESC,wp_posts.post_date DESC LIMIT 0, 2
這是一段wordpress程序生成的sql語句。。
(原文地址：http://www.tantengvip.com/2013/11/sql_calc_found_rows/)
