SQL statements under Big data are used

AMySQL limit large data volume paging optimization method

First create a table

CREATE TABLE ' Ipdatas ' (  ' id ' int (one) not null auto_increment,  ' uid ' int (8) is not null DEFAULT ' 0 '  , ' IPAddress ' varchar () Not NULL,  ' source ' varchar (255) default NULL,  ' track ' varchar (255) default NULL,  ' entrance ' varchar (255) DEFAULT null,  ' createdtime ' datetime NOT NULL DEFAULT ' 0000-00-00 00:00:00 ',  ' createddate ' date not NULL DEFAULT ' 0000-00-00 ',  PRIMARY key (' id '),  key ' uid ' (' uid ')) Engine=myisam auto_increment=68293856 DEFAULT Charset=utf8;

Inserting large amounts of data 1 million

begindeclare I int; Set i = 10001;   Myloop:loop                      If i = 100000 then           leave Myloop;          End If;                    /* Do something */          --loop output information insert INTO ' ipdatas ' (' uid ', ' ipaddress ', ' source ', ' track ', ' Entrance ', ' createdtime ', ' CreatedDate ') VALUES (i, ' 127.0.0.1 ', ' Wanglitao ', ' Guoyanhui ', ' 127.0.0.1 ', now (), now ());      Set i = i +1;    /* Loop End */end      loop myloop; end

Optimization statements

Original statement

SELECT * from Ipdatas ORDER by ID limit 900000,100

Rows affected: 0
Time: 16.408s

Optimization statements

Select * from Ipdatas Where id>= (
Select ID from Ipdatas ORDER by ID limit 900000,1
) limit 100;

SQL statements under Big Data use