Comparison of two solutions to paging queries in Mysql_PHP tutorial

There are two ways to perform paging queries in mysql. One is to use COUNT(*). The specific code is as follows

Copy the code The 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 code The code is as follows:

SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10;
SELECT FOUND_ROWS();

After calling SQL_CALC_FOUND_ROWS in the second way, the number of rows queried by the WHERE statement will be placed in FOUND_ROWS(). The second time you only need to query FOUND_ROWS() to find out How many lines are there?

Discuss the advantages and disadvantages of these two methods:
First of all, in terms of atomicity, the second one is definitely better than the first one. The second type can ensure the atomicity of the query statement. The first type will naturally cause inaccurate results when additional operations modify the table between the two requests. The second type will not. But it is a pity that when a general page needs to be displayed in paging, the paging results are often not required to be very accurate. That is, it does not matter whether the total number returned by paging is 1 larger or smaller than 1. So in fact, atomicity is not the focus of our paging.

Look at the efficiency below. This is very important. The paging operation is used very heavily on every website, and the query volume is naturally also large. Since no matter which type, the paging operation will inevitably involve two SQL queries, there are many 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’s article mentioned the concept of covering index. Simply put, it means how to only make the query return the results according to the index without performing table query

See his other article for details:

MySQL Covering Index

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

Experiment
Combined these articles and conducted an experiment:

Table:

Copy code 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 b and a are used to create an index. , so the covering index will not be used when querying select *. Only when selecting a will the covering index

Copy code The 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 '============================================' . "rn";

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

echo '================================ ==========' . "rn";

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

echo '============================ ==============' . "rn";

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

echo '================== =========================' . "rn";

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

The returned result:

It’s the same as what Lao Wang’s article said. The fourth query of SQL_CALC_FOUND_ROWS not only does not use the covering index, but also requires a full table query, and the third query of COUNT(*), and select * uses index, and does not perform a full table query, so it is so large difference.

Summary
PS: Also a reminder, there will be such a big difference between queries three and four when using MyISAM, but if you use InnoDB, there will not be such a big difference.

So I came to the conclusion that if the database is InnoDB, I still tend to use SQL_CALC_FOUND_ROWS

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

http://www.bkjia.com/PHPjc/326867.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/326867.htmlTechArticleThere are two ways to perform paging query in mysql. One is to use COUNT(*). The specific code is copied as follows The code is as follows: SELECT COUNT(*) FROM foo WHERE b = 1; SELECT a FROM foo WHERE b...