Two solutions for paging queries in MySQL compare _php instance

In MySQL, there are two ways of paging query, one is using count (*), the specific code is as follows

Copy Code code as follows:

SELECT COUNT (*) from foo WHERE b = 1;
SELECT A from foo WHERE b = 1 LIMIT 100, 10;

Another is the use of sql_calc_found_rows

Copy Code code as follows:

SELECT sql_calc_found_rows A from foo WHERE b = 1 LIMIT 100, 10;
SELECT found_rows ();

The second way to invoke Sql_calc_found_rows is to place the number of rows in the WHERE statement query in Found_rows (), and the second time only to query Found_rows () to find out how many rows there are.

Discuss the pros and cons of these two approaches:
First of all, Atomicity, the second is certainly better than the first. The second can guarantee the atomicity of query statements, the first one when there are additional operations between two requests that modify the table, the result is naturally inaccurate. And the second is not. However, it is very unfortunate that the General page needs to be displayed when the page, often does not require the results of pagination is very accurate. That is, it does not matter whether the total totals returned by paging are 1 or small 1. So in fact, atomicity is not the focus of our paging.

Look at the efficiency below. This is very important, paging operations on each site is very large use of the query volume is also very large. Either way, the paging operation is bound to have two SQL queries, so there's 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 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 the concept of covering index, in simple terms is how to only let the query return results based on the index, without table query

A specific look at his other article:

MySQL covering Index

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

Experiment
Combining these several articles, do the experiments:

Table:

Copy Code code as follows:

CREATE TABLE IF not EXISTS ' foo ' (
' A ' int (a) unsigned not NULL auto_increment,
' B ' int (a) unsigned not NULL,
' C ' varchar not NULL,
PRIMARY KEY (' a '),
KEY ' Bar ' (' B ', ' a ')
) Engine=myisam;

Note that this is done using B,a to make an index, so the query select * will not use the covering index, select a will be used to covering index

Copy Code code as follows:

<?php

$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";

The result returned:

It is the same as the article in Lao Wang. Fourth query sql_calc_found_rows because not only is not used to covering index, also need to make a full table query, and the third query count (*), and select * has to use to index, and did not make full table query, so there is so much difference.

Summarize
PS: Another reminder, here is the use of MyISAM will appear three and four of the query is so large, but if the use of InnoDB, there will not be such a big difference.

So I came to the conclusion that if the database was InnoDB, I would prefer to use sql_calc_found_rows

Conclusion: The performance of Sql_calc_found_rows and COUNT (*) is high when both uses covering index, which is high in the case where covering index is not used. So pay attention to this when you use it.