Some advanced uses of limit in mysql

Usually when we use sql statements, we use some relatively simple usages. Below, I will use some examples to talk about some advanced usage methods of limit in Mysql.

mysql limit efficiency:

select `id`,`title`,`descript`,`created` from myvbga_table where click = xxx limit offset, limit; //Summary: If there is no blob/text field and the single-line record is relatively small, you can set the limit larger, which will speed up the process.

limit offset value is relatively small:

select `id`,`title`,`descript`,`created` from vbga_table limit 10,10 //Multiple times Run, the time remains between 0.0004-0.0005

Select `id`,`title`,`describe`,`created` From vbga_table Where click ＞=(Select click From vbga_table Order By click limit 10,1 ) limit 10 //Multiple runs, the time remains between 0.0005-0.0006, mainly 0.0006

limit offset value is relatively large:

select `id`, `title`,`describe`,`created` from vbga_table limit 10000,10 //Run multiple times, the time remains around 0.0187

Select `id`,`title`,`descript`,`created` From vbga_table Where click ＞=(Select click From vbga_table Order By click limit 10000,1) limit 10 //Run multiple times, the time remains at around 0.0061, only 1/3 of the former. It can be expected that the larger the offset, the better the latter.

Mysql limit usage:

The LIMIT clause can be used to force the SELECT statement to return the specified number of records

SELECT `id`,` title`,`descript`,`created` FROM vbga_table LIMIT [offset,] rows | rows OFFSET offset

mysql> SELECT `id`,`title`,`descript`,`created` FROM vbga_table LIMIT 5 ,10; //Retrieve record rows 6-15 //In order to retrieve all record rows from a certain offset to the end of the recordset, you can specify the second parameter as -1:

mysql> SELECT `id`,`title`,`describe`,`created` FROM vbga_table LIMIT 95,-1; //Retrieve record rows 96-last. //If only one parameter is given, it means returning the maximum number of record rows:

mysql> SELECT `id`,`title`,`describe`,`created` FROM vbga_table LIMIT 5; //Retrieve the first 5 record rows//In other words, LIMIT n is equivalent to LIMIT 0 ,n.

mysql limit subquery usage example:

select `id`,`title`,`descript`,`created` from vbga_table where id in (select t. id from (select `id`,`title`,`descript`,`created` from vbga_table limit 10)as t)

mysql limit offset usage:

SELECT keyword FROM `zjoe_table` WHERE advertiserid='59' order by keyword LIMIT 2 OFFSET 1; //For example, in this SQL, limit is followed by 2 pieces of data, and offset is followed by reading from the 1st piece.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~

SELECT `keyword` FROM `zjoe_table` WHERE advertiserid='59' ORDER BY keyword LIMIT 2,1; //And this SQL, after limit is to start reading from the 2nd item, and read 1 piece of information.

Usage of limit variable in mysql stored procedure

CREATE PROCEDURE Getble_table(_id int,_limit int)

BEGIN

PREPARE s1 FROM 'SELECT `id`,`title`,`descript`,`created` FROM ble_table WHERE Cityid=? ORDER BY sendtime DESC LIMIT ?';

set @a=_id;

set @b=_limit;

EXECUTE s1 USING @a,@b;

DEALLOCATE PREPARE s1;

END;

The above is what I summarized Some advanced usage of limit in Mysql, I hope it will be helpful to everyone in the future.
Related articles:

How to read data through PHP MySQL

PHP related to connecting to MySQL Knowledge and its operations

The above is the detailed content of Some advanced uses of limit in mysql. For more information, please follow other related articles on the PHP Chinese website!