Use SQL _CALC_FOUND_ROWS as a query statement

Mysql> select SQL _CALC_FOUND_ROWS * FROM tbl_name-> WHERE id> 100 LIMIT 10;
Mysql> select FOUND_ROWS ();

With SQL _CALC_FOUND_ROWS, you can prepare the number of records that meet the where condition in advance during the query, and then you can obtain the total number of records by executing a select FOUND_ROWS () statement subsequently.

This method has a side effect. When SQL _CALC_FOUND_ROWS is used, the query cache cannot be used. In special cases, the query cache may lead to performance loss.

For example, in an article table, all articles have primary key IDs and CREATE_TIME DESC indexes. In this way
When you SELECT * from article order by id desc limit 10 or CREATE_TIME desc limit 20, the database engine can return the latest ARTICLE based on the index, regardless of how many matching records you have, however, after SQL _CALC_FOUND_ROWS is used, the engine has to scan the entire table to determine the number of all records.

However, SQL _CALC_FOUND_ROWS is very suitable for the case where statements are extremely complex and time-consuming. In applications that frequently use queries, this method can improve database performance by 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.
(Use SQL _CALC_FOUND_ROWS to perform Count for each page query; Use count (*) to cache results by yourself)

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.
(Only show the top and bottom pages; the program can easily know whether there is a previous page; judge the next page, you only need to query one more, you can determine whether there is a next record .)

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 (region = Region) left join wp_wti_like_post on wp_wti_like_post.post_id = wp_posts.ID WHERE 1 = 1 AND (post_date_gmt> '2017-11-08 15:37:48 ') AND (latency 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 limit desc, wp_posts.post_date desc limit 0, 2

This is an SQL statement generated by a wordpress program ..

(Original address: http://www.tantengvip.com/2013/11/ SQL _calc_found_rows)