Is the sql_calc_found_rows of MySQL really slow?

A paging program is typically composed of two sql:

SELECT COUNT (*) from ... WHERE ....
SELECT ... From ... WHERE LIMIT ...

If you use Sql_calc_found_rows, a SQL will do the following:

SELECT sql_calc_found_rows ... From ... WHERE LIMIT ...

After the data is obtained, the number of results without limit can be obtained by found_rows ():

SELECT Found_rows ()

It seems that sql_calc_found_rows should be faster than count (*), but the reality is not so simple, see:

To Sql_calc_found_rows or not to sql_calc_found_rows?

Speaking with data, it proves that count (*) is faster relative to sql_calc_found_rows. But I think this conclusion is not comprehensive, in some cases, sql_calc_found_rows more advantages, see my experiment:

The table structure 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;

Import some test data:

for ($i = 0; $i <10000; $i + +) {
mysql_query ("INSERT into foo SET b=round (RAND () *10), C=md5 ({$i})");
}

Test the Count (*) method first:

$start = Microtime (true);
for ($i = 0; $i < $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;

Result output (data size depends on test machine performance): 0.75777006149292

Re-Test Sql_calc_found_rows mode:

$start = Microtime (true);
for ($i = 0; $i < $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;

Result output (data size depends on test machine performance): 0.6681969165802

There is data on the truth, then why my experimental conclusions and MySQL performance blog conclusions contrary? This is because in the MySQL Performance blog experiment, the COUNT (*) query is the execution of the covering index, and sql_calc_found_rows is the execution of the table query; In my experiment, because I defined the proper index, COUNT (*) and sql_calc_found_rows are executed covering Index, so the conclusion is different.

Since the use of covering index means that you can no longer use the form of select *, you can only use a form like select ID, which can be found in the index, so where does the actual data we need come from? This is very simple, with the primary key, the actual data can be obtained through the Key/value form of the cache, such a structure is very common.

Conclusion: Sql_calc_found_rows If the covering index is executed, it is very fast! On the other hand, if count (*) and sql_calc_found_rows can only be retrieved through a table query, the sql_calc_found_rows is also faster than COUNT (*) when paging, and the reader can test itself.

http://kb.cnblogs.com/page/82986/

Sql_calc_found_rows.

SELECT sql_calc_found_rows ... From ... WHERE LIMIT ...

The so-called sql_calc_found_rows is when executing a query with limit, with statistics on how many results will be output if no limit is added.

After the data is obtained, the number of results without limit can be obtained by found_rows ():

SELECT Found_rows ()

Is the sql_calc_found_rows of MySQL really slow?