MySQL Paging stored procedure

There is a lot of information about MySQL paging stored procedures on the Web, but the content is similar. As a beginner, reference the MySQL stored procedure as follows:

<p> </p><p>delimiter $$</p><p>use ' database1 ' $$--database name </p><p>drop PROCEDURE if EXISTS ' query_pagination ' $$ --paging stored procedure name, existing delete </p><p>create <a Target=_blank href= " mailto:definer= ' root ' @ '% ' >definer= ' root ' @ '%</a> ' PROCEDURE ' query_pagination ' ( --Create a new paging stored procedure   In _fields varchar (2000),--display field   in _tables TEXT,--table name   in _where VARCHAR (),--  where condition, nullable &nbsp ; In _orderby VARCHAR (200),--Sort condition, nullable   in _pageindex int,--start page   in _pagesize int,--size per page   out _totalcou NT INT,--Total number of rows   out _pagecount INT--  total pages) begin  SET @startrow = _pagesize * (_pageindex-1);  SE T @pagesize = _pagesize;  Set @rowindex = 0;  Set @strsql = CONCAT (    ' Select Sql_calc_found_ Rows @rowindex: [email protected]+1 as RowNumber, ',--show the line number of each bar     _fields,    ' from ',    _tables,    case      ifnull (_where, ')       when '        Then '       ELSE CONCAT (' where ', _where)     end,  & nbsp;   case      ifnull (_orderby, ')       when '       then "      ELSE CONCAT (' ORDER by ', _orderby)   & nbsp end,      ' limit ',    @startRow,    ', ',    @  pagesize );  PREPARE strSQL from @strsql;  EXECUTE strsql;  SET _totalcount = found_rows ();  if (_totalcount <= _pagesize)   then SET _pagecount = 1;  ELSE IF (_totalcount% _pagesize > 0)   T HEN Set _pagecount = _totalcount/_pagesize + 1;  ELSE SET _pagecount = _totalcount/_pagesize;  END IF; &nb Sp END IF; End$$</p><p>delimiter; </P><p> </p> 

The paging stored procedure above does work well, but when the amount of data reaches millions, the discovery speed drops and the following statement is executed with explain:

EXPLAIN SELECT sql_calc_found_rows @rowindex: [email protected]+1 as RowNumber from View_visitregisterinfo WHERE cardtype = ' 1 ' ORDER by TableID DESC LIMIT 0,1000;

The results of the implementation are as follows:

As you can see, although the limit is used, the entire table is still retrieved, resulting in a slow query. The execution time is 16 seconds.

Remove sql_calc_found_rows @rowindex: [email protected]+1 as RowNumber, query statement changed to explain SELECT * from View_visitregisterinfo WHERE 1=1 and cardtype= ' 1 ' ORDER by TableID DESC LIMIT 0,1000; The results of the implementation are as follows:

As you can see, the number of rows executed is 1000, the execution time is 0.038s, and the time is much faster.

So, modify the stored procedure to:

DELIMITER $ $USE ' speednew ' $$--database name drop PROCEDURE IF EXISTS ' query_pagination ' $$--paging stored procedure name, existing delete create definer= ' root ' @ '% ' PROCEDURE ' query_pagination ' (--Create new paging stored procedure in _fields VARCHAR (2000),--display field in _tables TEXT,--table name in _where varchar,---where condition, nullable in _orderby VARCHAR (200),--Sort condition, nullable in _pageindex int,--start page in _pagesize int,--each  Page size out _totalcount int,--Total number of rows out _pagecount INT--Total pages) BEGIN SET @startrow = _pagesize * (_pageindex-1);  SET @pagesize = _pagesize;  SET @rowindex = 0;       SET @strsql = CONCAT (' Select ', _fields, ' from ', _tables, Case ifnull (_where, ") when"        Then "ELSE CONCAT (' where ', _where) END, Case ifnull (_orderby,") when "then"  ELSE CONCAT (' ORDER by ', _orderby) END, ' limit ', @startRow, ', ', @pageSize);  PREPARE strSQL from @strsql;  EXECUTE strSQL;  SET _totalcount = Found_rows (); IF (_totalcount <= _pagesize) then SET _pagecount = 1;  ELSE IF (_totalcount% _pagesize > 0) then SET _pagecount = _totalcount/_pagesize + 1;  ELSE SET _pagecount = _totalcount/_pagesize;  END IF; END IF; end$ $DELIMITER;

Summing up the experience: when referring to existing things, be sure to make two changes according to the circumstances of the individual, instead of to suit their own, at the same time to understand the reasons for others to write the original.

MySQL Paging stored procedure