MySQL limit usage and MySQL limit optimization

MySQL tutorial limit usage and MySQL limit optimization
/*
MySQL limit usage November 08, 2007 14:291. SELECT * FROM tablename < conditional statement > limit 100,15

Start with 100 records after 15 (the actual fetch is 第101-115条 data)

2. Select * FROM tablename < conditional statement > limit 100,-1

Start after 100th-record of the last article

3. SELECT * FROM tablename < conditional statement > limit 15

Equivalent to limit 0,15. Query results take the first 15 data

To see the Limit optimization method, first save 1 million records below.
*/  
Set_time_limit (0);
mysql_connect (' localhost ', ' root ', ' root ');
mysql_select_db (' ACCC ');
mysql_query ("Set names ' gb2312 '");
Echo is importing data ... ';
for ($i =0 $i <=1000000; $i + +)
{
$sql = ' Insert Cn_loupan_news (Cityid,zid,tid,did,title,body,click, Litpic,sendname,header,recommend,hot,flash,setindex,opencomment,cityname,keywords,desciption,color,b,senddate, LID,NFROM,TITLE2,ZT) VALUES (' 11 ', ' 2 ', ' 1 ', ' 1 ', ' Test data ', ' test data ', ' the ', '/aa/gigf.gif ', ' aaaa ', ' 0 ', ' 0 ', ' 0 ', ' 0 ', ' 0 ', ' 0 ', ' 222 ', ' 222 ', ' Gsfdsgfds ', ' 1 ', ' 1 ', ' 124567124 ', ' 0 ', ' fdafda ', ' f ', ' Fdafa ');
mysql_query ($sql) or Die (Mysql_error ());
}
Echo ' successfully imported 1 million data ';
/*
Select ID
from ' cn_loupan_news '
ORDER BY id desc
Limit 800001, 20
Sort query time by primary keyword is displayed row 0-19 (20 total, query takes 0.2712 seconds)

Select *
from ' cn_loupan_news '
ORDER BY id desc
Limit 800001

Show Row 0-19 (20 total, query takes 4.3221 seconds)

From here to see how to select the ID Master index, the sorting speed is also ideal, if it is the * number again with the sort query time more than 4 seconds ah, not advisable method.

SELECT *
From Cn_loupan_news
Where ID >= (
Select ID
From Cn_loupan_news
Limit 800000, 1)
Limit 10

Show Row 0-9 (10 total, query takes 0.2456 seconds)

A little bit better.

Original articles reproduced in this site annotated from http://www.111cn.net/database/database.html