Comparison of two solutions for querying by page in Mysql

This article introduces two solutions for querying by page in Mysql. For more information, see the following two methods for querying by page in mysql: COUNT (*). The code is as follows:
The code is as follows:
Select count (*) FROM foo WHERE B = 1;
SELECT a FROM foo WHERE B = 1 LIMIT 100,10;

Another method is to use SQL _CALC_FOUND_ROWS.
The code is as follows:
SELECT SQL _CALC_FOUND_ROWS a FROM foo WHERE B = 1 LIMIT 100, 10;
SELECT FOUND_ROWS ();

In the second method, after SQL _CALC_FOUND_ROWS is called, the number of rows queried by the WHERE statement is placed in FOUND_ROWS (). in the second method, you only need to query FOUND_ROWS () to find out how many rows there are.

The advantages and disadvantages of the two methods are discussed:
First of all, atomicity is better than first. The second method ensures the atomicity of the query statement. The first is that when an additional operation is performed between two requests to modify the table, the result is naturally inaccurate. The second type does not. But it is a pity that when pages need to be displayed by page, the results of pages are usually not required to be very accurate. That is, it doesn't matter if the total number of pages returned is 1 or 1. So atomicity is not the focus of paging.

The following describes the efficiency. This is very important. paging operations are used on every website, and the query volume is naturally large. Either way, the paging operation will inevitably have two SQL queries, so there are a lot of comparisons about the performance of the two queries:

Is SQL _CALC_FOUND_ROWS really slow?

Http://hi.baidu.com/thinkinginlamp/item/b122fdaea5ba23f614329b14

To SQL _CALC_FOUND_ROWS or not to SQL _CALC_FOUND_ROWS?

Http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Lao Wang mentioned the covering index concept in this article. Simply put, it is how to make the query only return results based on the index without querying the table.

Let's take a look at another article:

Covering Index of MySQL

Http://hi.baidu.com/thinkinginlamp/item/1b9aaf09014acce0f45ba6d3

Lab
Based on these articles, the experiment is as follows:

Table:
The code is as follows:
Create table if not exists 'foo '(
'A' int (10) unsigned not null AUTO_INCREMENT,
'B' int (10) unsigned NOT NULL,
'C' varchar (100) not null,
Primary key ('A '),
KEY 'bar' ('B', 'A ')
) ENGINE = MyISAM;

Note that here we use B and a to create an index. Therefore, covering index is not used when querying select *. select a will use covering index.
The code is as follows:

$ Host = '1970. 168.100.166 ';
$ DbName = 'test ';
$ User = 'root ';
$ Password = '';

$ Db = mysql_connect ($ host, $ user, $ password) or die ('Db connect failed ');
Mysql_select_db ($ dbName, $ db );


Echo '===================================================== ==== '. "\ r \ n ";

$ Start = microtime (true );
For ($ I = 0; I I <1000; $ I ++ ){
Mysql_query ("SELECT SQL _NO_CACHE COUNT (*) FROM foo WHERE B = 1 ");
Mysql_query ("SELECT SQL _NO_CACHE a FROM foo WHERE B = 1 LIMIT 100,10 ");
}
$ End = microtime (true );
Echo $ end-$ start. "\ r \ n ";

Echo '===================================================== ==== '. "\ r \ n ";

$ Start = microtime (true );
For ($ I = 0; I I <1000; $ I ++ ){
Mysql_query ("SELECT SQL _NO_CACHE SQL _CALC_FOUND_ROWS a FROM foo WHERE B = 1 LIMIT 100, 10 ");
Mysql_query ("SELECT FOUND_ROWS ()");
}
$ End = microtime (true );
Echo $ end-$ start. "\ r \ n ";

Echo '===================================================== ==== '. "\ r \ n ";

$ Start = microtime (true );
For ($ I = 0; I I <1000; $ I ++ ){
Mysql_query ("SELECT SQL _NO_CACHE COUNT (*) FROM foo WHERE B = 1 ");
Mysql_query ("SELECT SQL _NO_CACHE * FROM foo WHERE B = 1 LIMIT 100,10 ");
}
$ End = microtime (true );
Echo $ end-$ start. "\ r \ n ";

Echo '===================================================== ==== '. "\ r \ n ";

$ Start = microtime (true );
For ($ I = 0; I I <1000; $ I ++ ){
Mysql_query ("SELECT SQL _NO_CACHE SQL _CALC_FOUND_ROWS * FROM foo WHERE B = 1 LIMIT 100, 10 ");
Mysql_query ("SELECT FOUND_ROWS ()");
}
$ End = microtime (true );
Echo $ end-$ start. "\ r \ n ";

Returned results:

It is the same as the article in old Wang. The fourth query, SQL _CALC_FOUND_ROWS, not only does not use covering index, but also requires full table query. The third query, COUNT (*), and select * uses index, there is no full table query, so there is such a big difference.

Summary
PS: In addition, the difference between the use of MyISAM and the use of MySQL is so big, but if InnoDB is used, there will be no such big difference.

So I came to the conclusion that if the database is InnoDB, I would prefer to use SQL _CALC_FOUND_ROWS.

Conclusion:The performance of SQL _CALC_FOUND_ROWS and COUNT (*) is high when covering index is used, and the performance is high when covering index is not used. So pay attention to this when using it.