Mysql paging stored procedure and mysql Stored Procedure

Mysql paging stored procedure and mysql Stored Procedure

There is a lot of information about the mysql paging stored procedure on the Internet, 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' $ -- Name of the paging stored procedure, if yes, delete </p> <p> CREATE <a target = _ blank href = "mailto: DEFINER = 'root' @ '% "> DEFINER = 'root' @' % </a> 'Procedure 'query _ Pagination '(-- create a paging stored procedure in _ fields VARCHAR (2000 ), -- display field IN _ tables TEXT, -- table name IN _ where VARCHAR (2000), -- where condition, can be empty IN _ orderby VARCHAR (200), -- sorting condition, can be empty IN _ pageindex INT, -- start page IN _ pagesize INT, -- size of each page OUT _ totalcount INT, -- total number of rows OUT _ pagecount INT -- total number of pages) begin set @ startrow = _ pagesize * (_ pageindex-1); SET @ pagesize = _ pagesize; SET @ rowindex = 0; SET @ strsql = CONCAT ('select SQL _calc_found_rows @ rowindex: = @ rowindex + 1 as rownumber, ', -- display the row number _ 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 $ </p> <p> DELIMITER; </p>

The paging stored procedure is indeed very useful, but when the data volume reaches a million level, the detection speed will decrease. Use explain to execute the following statement:

Explain select SQL _CALC_FOUND_ROWS @ rowindex: = @ rowindex + 1 AS rownumber FROM view_visitregisterinfo WHERE CardType = '1' ORDER BY tableid DESC LIMIT;

The execution result is as follows:

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

Remove SQL _CALC_FOUND_ROWS @ rowindex: = @ rowindex + 1 AS rownumber. Change the query statement to EXPLAIN SELECT * FROM view_visitregisterinfo WHERE 1 = 1 AND CardType = '1' ORDER BY tableid DESC LIMIT; the execution result is as follows:

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

Therefore, modify the stored procedure:

DELIMITER $ USE 'speednew' $ -- Database Name drop procedure if exists 'query _ Pagination '$ -- Name of the paging stored PROCEDURE, if yes, delete create definer = 'root' @ '%' PROCEDURE 'query _ Pagination '(-- CREATE a paging stored procedure in _ fields VARCHAR (2000 ), -- display field IN _ tables TEXT, -- table name IN _ where VARCHAR (2000), -- where condition, can be empty IN _ orderby VARCHAR (200), -- sorting condition, can be empty IN _ pageindex INT, -- start page IN _ pagesize INT, -- size of each page OUT _ totalcount INT, -- total number of rows OUT _ pagecount INT -- total number of 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;

 

 

 

Conclusion: When referencing an existing item, you must modify it based on your individual situation to suit yourself. At the same time, you must understand the reasons for others to write it as originally.