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

This article is mainly about the summary of Limit performance of MySQL. If you are interested, let's take a look at this article. I believe it is of some reference value to everyone after reading the summary of Limit performance of MySQL.

Paging queries for MySQL are usually implemented through limit.

The basic use of limit for MySQL is simple. 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 ideas:

Avoid scanning too many records when there is a large amount of data

Resolve:

The paging mode of the subquery or JOIN paging mode.

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.

Take a table of 800000 pieces of data in a real production environment as an example to compare the query time before and after optimization:

-- traditional limit, file scan [SQL] SELECT * FROM tableName ORDER BY id LIMIT 500000 WHERE t1.id 2; affected lines: 0 time: 5.371s-subquery mode, index scan [SQL] SELECT * FROM tableName WHERE id > = (SELECT id FROM tableName ORDER BY id LIMIT 500000, 1) LIMIT 2; affected lines: 0 time: 0.274s-JOIN paging mode [SQL] SELECT * FROM tableName AS T1 JOIN (SELECT id FROM tableName ORDER BY id desc LIMIT 500000, 1) AS T2 WHERE 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.