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

The main purpose of this article is to tell you briefly about the advanced usage of limit in mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to use the advanced usage of limit in mysql can bring you some practical help.

Mysql limit efficiency:

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

The limit offset value is relatively small:

Select `id`, `title`, `describle`, `created` from vbga_table limit 10 run 10 / / multiple times, keeping the time between 0.0004 and 0.0005

Select `id`, `title`, `describle`, `created` From vbga_table Where click > = (Select click From vbga_table Order By click limit 10d1) limit 10 / / runs many times, and the time is kept between 0.0005 and 0.0006, mainly 0.0006

The limit offset value is relatively large:

Select `id`, `title`, `describle`, `created` from vbga_table limit 10 / / times, keeping the time around 0.0187

Select `id`, `title`, `describle`, `created` From vbga_table Where click > = (Select click From vbga_table Order By click limit 10000 limit 1) run 10 / / many times, and the time is about 0.0061. Only the former has 1 / 3. It can be expected that the larger the offset, the better the latter.

Limit usage of Mysql:

The LIMIT clause can be used to force the SELECT statement to return a 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 510; / / 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-1:

Mysql > SELECT `id`, `title`, `describle`, `created` FROM vbga_table LIMIT 95mai Murray 1; / / retrieve record lines 96-last. / / if only one parameter is given, it represents the maximum number of record rows returned:

Mysql > SELECT `id`, `title`, `describle`, `created` FROM vbga_table LIMIT 5; / / retrieve the first five record rows / / in other words, LIMIT n is equivalent to LIMIT 0jinn.

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)

Mysql limit offset usage:

SELECT keyword FROM `zjoe_ table` WHERE advertiserid='59' order by keyword LIMIT 2 OFFSET 1; / / for example, this SQL, limit is followed by two pieces of data, and offset is read from item 1.

~

SELECT `keyword` FROM `zjoe_ Table` WHERE advertiserid='59' ORDER BY keyword LIMIT 2,1; / / this SQL,limit is followed by reading from Article 2, reading 1 message.

Usage of limit variables in mysql stored procedures

CREATE PROCEDURE Getble_table (_ id int,_limit int)

BEGIN

PREPARE S1 FROM 'SELECT `id`, `title`, `describle`, `created` FROM ble_table WHERE Cityid=? ORDER BY sendtime DESC LIMIT?'

Set @ a=_id

Set @ b=_limit

EXECUTE S1 USING @ a

DEALLOCATE PREPARE s1

This is the end of how to use the advanced usage of limit in mysql. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.