mysql limit advanced usage examples-Mysql Tutorial-php.cn

mysql limit advanced usage examples

黄舟

Release： 2017-02-06 15:39:11

Original

1084 people have browsed it

mysql limit efficiency:

select `id`,`title`,`describle`,`created` from myvbga_table where click = xxx limit offset, limit; //总结：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度。

Copy after login

limit offset value is relatively small:

select `id`,`title`,`describle`,`created` from vbga_table limit 10,10 //多次运行，时间保持在0.0004-0.0005之间
Select `id`,`title`,`describle`,`created` From vbga_table Where click ＞=(Select click From vbga_table Order By click limit 10,1) limit 10 
//多次运行，时间保持在0.0005-0.0006之间，主要是0.0006

Copy after login

limit offset value is relatively large:

select `id`,`title`,`describle`,`created` from vbga_table limit 10000,10 //多次运行，时间保持在0.0187左右
Select `id`,`title`,`describle`,`created` From vbga_table Where click ＞=(Select click From vbga_table Order By click limit 10000,1) limit 10

Copy after login

//Multiple runs, time It remains at around 0.0061, which is 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`,`describle`,`created` FROM vbga_table LIMIT [offset,] rows | rows OFFSET offset
mysql> SELECT `id`,`title`,`describle`,`created` FROM vbga_table LIMIT 5,10; // 检索记录行 6-15 //为了检索从某一个偏移量到记录集的结束所有的记录行，
可以指定第二个参数为 -1：
mysql> SELECT `id`,`title`,`describle`,`created` FROM vbga_table LIMIT 95,-1; // 检索记录行 96-last. //如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT `id`,`title`,`describle`,`created` FROM vbga_table LIMIT 5; //检索前 5 个记录行 //换句话说，LIMIT n 等价于 LIMIT 0,n。

Copy after login

mysql limit subquery usage example:

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

Copy after login

mysql limit offset usage:

SELECT keyword FROM `zjoe_table` WHERE advertiserid=&#39;59&#39; order by keyword LIMIT 2 OFFSET 1; //比如这个SQL ，limit后面跟的是2条数据，offset后面是从第1条开始读取

Copy after login

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

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

Usage of limit variable in mysql stored procedure

CREATE PROCEDURE Getble_table(_id int,_limit int)
BEGIN
PREPARE s1 FROM &#39;SELECT `id`,`title`,`describle`,`created` FROM ble_table WHERE Cityid=? ORDER BY sendtime DESC LIMIT ?&#39;;
set @a=_id;
set @b=_limit;
EXECUTE s1 USING @a,@b;
DEALLOCATE PREPARE s1;
END;

Copy after login

Note: You must use "?" where parameters need to be passed. ", the statement after the first FROM must be enclosed in ''.

The above is the content of mysql, limit, and advanced usage. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!