Summary of MySQL paging technology

Summary of MySQL paging technology

-- Method 1: directly use the SQL statements provided by the database
--- Statement style: MySQL can use the following method: SELECT * FROM table name limit m, N
--- Applicable scenario: Suitable for scenarios with a small amount of data (hundreds/thousands of tuples)
--- Cause/disadvantage: full table scan is slow and some database result sets return instability (for example, 1, 2, 3, or 2, 1, 3 ). the Limit is that N outputs are extracted from the M position of the result set, and the rest are discarded.

--- Method 2: Create a primary key or unique index and use the index (assuming 10 entries per page)
--- Statement style: MySQL, the following method can be used: SELECT * FROM table name WHERE id_pk> (pageNum * 10) LIMIT M
--- Applicable scenario: Suitable for scenarios with a large amount of data (tens of thousands of RMB)
--- Cause: the index scan speed will be very fast. A friend suggested that data is not sorted by pk_id, so data is missing. Only method 3 can be used.

--- Method 3: index-based re-sorting
--- Statement style: MySQL, the following method can be used: SELECT * FROM table name WHERE id_pk> (pageNum * 10) order by id_pk ASC LIMIT M
--- Applicable scenario: Suitable for scenarios with a large amount of data (tens of thousands of groups ). it is recommended that the column object after order by is a primary key or unique, so that the ORDERBY operation can use the index to be eliminated, but the result set is stable (for a stable meaning, see method 1)
--- Cause: Index scanning will speed up. However, for MySQL sorting operations, only ASC does not have DESC (DESC is false, and real DESC will be performed in the future. We look forward ...).

--- Method 4: Use prepare Based on the index (the first question mark indicates pageNum, and the second question mark? Number of member groups per page)
--- Statement style: MySQL can use the following method: PREPARE stmt_name from select * FROM table name WHERE id_pk> (? *?) Order by id_pk ASC LIMIT M
--- Applicable scenario: large data volume
--- Cause: the index scan speed is faster. The prepare statement is faster than the general query statement.

--- Method 5: Stored Procedure class (preferably the above method 4)
--- Statement style: no longer given
--- Applicable scenarios: large data volumes. recommended methods by the author
--- Cause: the operation is encapsulated on the server, which is relatively faster.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: