PhpMySQL and paging efficiency. The most basic paging method: SELECT... FROM... WHERE... ORDERBY... LIMIT... in the case of small data volumes, such SQL is enough. The only problem that needs to be noted is that the most basic paging method of cable is used:
SELECT... FROM... WHERE... order by... LIMIT...
In the case of small data volumes, such SQL statements are enough. The only problem that needs to be noted is that indexes are used:
For example, if the actual SQL statement is similar to the following statement, it is better to create a composite index on the category_id and id columns:
SELECT * FROM articles WHERE category_id = 123 order by id LIMIT 50, 10
Subquery paging method:
As the amount of data increases, the number of pages will increase. the SQL statements on the last few pages may be similar:
SELECT * FROM articles WHERE category_id = 123 order by id LIMIT 10000, 10
In a word, the more paging goes, the larger the offset of the LIMIT statement, and the slower the speed.
In this case, we can use subqueries to improve paging efficiency, which is roughly as follows:
SELECT * FROM articles WHERE category_id = 123 AND id> = (
SELECT id FROM articles order by id LIMIT 10000, 1
) LIMIT 10
----------------------------------------
In practice, we can use a method similar to the policy mode to process pages. for example, if the number of pages is less than one hundred, we use the most basic paging method. if the number of pages is greater than one hundred, we use the paging method of subquery.
Explain SELECT... FROM... WHERE... ORDERBY... LIMIT... in the case of small data volumes, such SQL is enough, and the only problem that needs to be paid attention is to ensure that the cable is used...
