Paging of MySQL's efficient access process

Data over is 4-5 times faster than normal paging.

 

Procedure p_pagelist (m_pageno int, m_perpagecnt int, m_indexcolumn varchar (100), m_column varchar (1000), m_table varchar (1000), m_condition varchar (1000), m_orderby varchar (200 ), inout m_totalpagecnt INT)
Begin

If m_totalpagecnt> = 1 then
Set @ sqlcnt = Concat ('select count (1) into @ pagecnt from', m_table ); -- {*********************************************************************
If m_condition is not null and m_condition <> ''then
Set @ sqlcnt = Concat (@ sqlcnt, 'where', m_condition );
End if;
Prepare s_cnt from @ sqlcnt;
Execute s_cnt;
Deallocate prepare s_cnt;
Set m_totalpagecnt = @ pagecnt;
Else
Set @ pagecnt = 1; -- interval» è ° interval °
Set @ limitstart = (m_pageno-1) * m_perpagecnt;
Set @ limitend = m_perpagecnt;
Set @ SQL = Concat ('select', m_column, 'from', m_table); -- ää» ~~~~~~~
Set @ wsql = Concat ('select', m_indexcolumn, 'from', m_table); -- revoke *************
If m_condition is not null and m_condition <> ''then
Set @ SQL = Concat (@ SQL, 'where', m_condition );
Set @ wsql = Concat (@ wsql, 'where', m_condition );
End if;

If @ limitstart <1000 then
If m_orderby is not null and m_orderby <> ''then
Set @ SQL = Concat (@ SQL, 'ORDER BY', m_orderby );
End if;
Set @ SQL = Concat (@ SQL, 'limit', @ limitstart, ',', @ limitend );
Else
If m_orderby is not null and m_orderby <> ''then
Set @ wsql = Concat (@ wsql, 'ORDER BY', m_orderby );
If m_condition is not null and m_condition <> ''then
Set @ SQL = Concat (@ SQL, 'and', m_indexcolumn, '> = (', @ wsql, 'limit', @ limitstart, ', 1 )', 'ORDER BY', m_orderby, 'limit', @ limitend );
Else
Set @ SQL = Concat (@ SQL, 'where', m_indexcolumn, '> = (', @ wsql, 'limit', @ limitstart, ', 1 )', 'ORDER BY', m_orderby, 'limit', @ limitend );
End if;
Else
If m_condition is not null and m_condition <> ''then
Set @ SQL = Concat (@ SQL, 'and', m_indexcolumn, '> = (', @ wsql, 'limit', @ limitstart, ', 1 )', 'limit', @ limitend );
Else
Set @ SQL = Concat (@ SQL, 'where', m_indexcolumn, '> = (', @ wsql, 'limit', @ limitstart, ', 1 )', 'limit', @ limitend );
End if;
End if;
End if;

Prepare record from @ SQL;
Execute record;
Deallocate prepare record;

 

/*

Select @ SQL;
*/

 

 

 

End if;
End