PHP paging how to improve the efficiency of the problem

Now, when you write a page, you have to figure out the total number of data bars, and then how many pages are calculated based on the number of pages per page.

This is the time to write the statement:

$req = date > 1200000000
Select COUNT (*) from Test WHERE {$req}
Then it is based on the total number of bars and the number of bars displayed per page $limit
SELECT * FROM Test WHERE {$req} LIMIT {$limit}

I feel this kind of execution efficiency is not high, can I just select once?

Reply content:

Now, when you write a page, you have to figure out the total number of data bars, and then how many pages are calculated based on the number of pages per page.

This is the time to write the statement:

$req = date > 1200000000
Select COUNT (*) from Test WHERE {$req}
Then it is based on the total number of bars and the number of bars displayed per page $limit
SELECT * FROM Test WHERE {$req} LIMIT {$limit}

I feel this kind of execution efficiency is not high, can I just select once?

If you are worried about performance, you can discard the "total pages", please refer to
Yahoo's efficient pagination Using MySQL

To use Sql_calc_found_rows, please read first
To Sql_calc_found_rows or not to sql_ ...

Mysql in the paging with limit query, the farther back, the lower the efficiency, I say my own approach, the general primary key ID is self-increment, and with where condition data is not exactly the data to be obtained. For example, a table with a table we_user_score points
UID (_PK) Username (user name) score (integral)

Suppose there are 100 data where the integral distribution is 10-100 of this interval. This time, want to get score>30 data per page 5 article

Select Uid,username,score from we_user_score where score>30 limit 0, 5;

But in the second page of the query, SQL can write like this.

Select Uid,username,score from We_user_score where uid>=5 and score>30 limit 5, 5;

And so on, so in the case of large data volume, the speed will improve the more obvious, this should belong to a small detail it, hehe

In fact, this kind of big data is much quicker in efficiency. Very nice one to share.

MySQL on the Select count (*) is optimized, so the actual query consumption is very small, LZ feel that the execution efficiency is not good, it is best to look at the MySQL explain data bar, all the data to prevail.

You can try SQL_CALC_FOUND_ROWS it, although it is also two queries, but MySQL itself does not need to perform an index lookup again.

Usage

SELECT sql_calc_found_rows * from Test WHERE {$req} LIMIT {$limit}

Execute immediately after completing the query

SELECT Found_rows ()

Some people say that in the case of concurrency, the results may be garbled, see here http://us3.php.net/manual/en/function ...

PS: You have the index on the query condition, do not worry too much about performance problems to do pre-optimization, wait until the performance is really a problem.

