Shulou(Shulou.com)06/01 Report--

Paging queries for MySQL are usually implemented through limit. Limit receives 1 or 2 integer parameters, if 2 parameters, the first is to specify the offset of the first to return record rows, and the second is to return the maximum number of record rows. The offset of the initial record row is 0. For compatibility with PostgreSQL, limit also supports limit # offset #.

Question:

For small offsets, there is no problem with directly using limit to query, but as the amount of data increases, the more paging back, the greater the offset of the limit statement and the slower the speed.

Optimization idea: avoid scanning too many records when there is a large amount of data

Solution: subquery paging or JOIN paging. The efficiency of JOIN paging and subquery paging is basically at the same level, and the time consumed is basically the same.

Let me give you an example. In general, the primary key of MySQL is a self-increasing number type, in which case you can use the following ways to optimize.

Taking a table of 60,000 pieces of data in a real production environment as an example, compare the query time before and after optimization:

-- traditional limit, file scan [SQL] SELECT * FROM tableName ORDER BY id LIMIT 5000 LIMIT 2; affected lines: 0 time: 0.171s SELECT-subquery mode, index scan [SQL] SELECT * FROM tableNameWHERE id > = (SELECT id FROM tableName ORDER BY id LIMIT 50000, 1) LIMIT 2; affected lines: 0 time: 0.035s Kortel-JOIN paging mode [SQL] SELECT * FROM tableName AS t1JOIN (SELECT id FROM tableName ORDER BY id LIMIT 50000, 1) AS t2WHERE t1.id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.