Two workarounds for paging queries in MySQL compare _php instances

There are two ways of paging queries in MySQL, one is the use of the count (*) method, the specific code is as follows
Copy CodeThe code is as follows:
SELECT COUNT (*) from foo WHERE b = 1;
SELECT A from foo WHERE b = 1 LIMIT 100, 10;

Another way is to use sql_calc_found_rows
Copy CodeThe code is as follows:
SELECT sql_calc_found_rows A from foo WHERE b = 1 LIMIT 100, 10;
SELECT found_rows ();

The second way to call Sql_calc_found_rows is to place the number of rows queried by the where statement in Found_rows (), and the second time just query Found_rows () to find out how many rows there are.

Discuss the pros and cons of these two approaches:
First of all, the second is better than the first. The second is to ensure the atomicity of the query statement, the first one when there are additional actions between two requests to modify the table, the result is naturally inaccurate. And the second is not. But unfortunately, the general page needs to be paginated, often does not require the results of the page is very accurate. That is, the total number of pages returned is large 1 or 1 is irrelevant. So in fact, atomicity is not our focus on paging.

See below for efficiency. This is very important, the paging operation on each site is very large, the query volume is naturally very large. Because either way, the paging operation will inevitably have two SQL queries, so there is a lot of comparison between the two query performance:

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 This article contains a reference to a covering index concept, simple is how to only let the query return results based on the index, without making a table query

See his other article specifically:

MySQL's covering Index

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

Experiment
Combine these articles to do the experiment:

table:
Copy CodeThe code is as follows:
CREATE TABLE IF not EXISTS ' foo ' (
' A ' int (ten) unsigned not NULL auto_increment,
' B ' int (ten) unsigned not NULL,
' C ' varchar (+) not NULL,
PRIMARY KEY (' a '),
KEY ' Bar ' (' B ', ' a ')
) Engine=myisam;

Note that this is the use of b,a to make an index, so query select * is not used when the covering index, select a will be used to covering index
Copy CodeThe code is as follows:

$host = ' 192.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 <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 <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 <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 <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";

Results returned:

And Lao Wang inside the article said is the same. Fourth query sql_calc_found_rows because not only is not used to covering index, but also need to make a full table query, and the third query count (*), and select * has been used to index, and did not make a full table query, so there is such a big difference.

Summarize
PS: Another reminder, here is the use of MyISAM will appear three and four of the query difference is so big, but if you use InnoDB, there will be no such a 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 the Sql_calc_found_rows and COUNT (*) is high in the case where both covering index is used, which is high in the case where the covering index is not used. So pay attention to this when you use it.

