Dave’s Blog

I have been writing a special web application framework around the n-tier pattern, and in the database layer I ran into a problem with MySQL.  Within a stored procedure you can order by a variable but not limit by a variable, which puts a small damper on pagination.  There are many threads on the Internet of outraged MySQL users, with several solutions but I didn’t really like them all.

The first solution I saw was to prepare the SQL statement as a string and execute it in the proc, replacing the limit parameters as such:

create procedure myProcedure (myLimitFrom, myLimitNumber, ....)
.....
set $sqlStmt = 'select myFields from tTable limit ?,?';
set @myLimitFrom = myLimitFrom;
set @myLimitNumber = myLimitNumber;
prepare STMT from @sqlStmt;
execute STMT using @myLimitFrom, @myLimitNumber;
drop prepare STMT;
....

The biggest problem with that one (besides how messy it is to look at) is that it is very inefficient.  The SQL will have to compile on-the-fly within the proc each run.

Another solution I saw was to assign a rowcount variable that increments each row and have a where clause that is in the rows needed.

set @n=0;
select myFields from tTable where (@n:=@n+1) between a and b;

That is nasty as well since it is allocating and working with a variable for each row in the table.  Over thousands of records with multiple database transactions it could become expensive.
Yet another solution was to select where the ID’s are between a range, but if a field is physically deleted, then it would skip a row.

The best I can come up with is to create a temporary table and insert the ID’s into it with an auto_number column, then select out again in the range of the auto_number column joined to the table (Thanks for the hint, Paul G).

create procedure sptCitySelect(... myLimitFrom bigint unsigned, myLimitNumber bigint unsigned ...)
begin
...
 	create temporary table tmpIDResult (RowNumber bigint unsigned not null auto_increment, TableID bigint unsigned not null, index (RowNumber), primary key (TableID)) ;
        insert into tmpIDResult (TableID)
 	select CityID
 	from tCity
 	where ...
 	order by ...        

        select t.CityID, t.City, t.StateID, t.Deleted
 	from tmpIDResult r
 		join tCity t on t.CityID = r.TableID
 	where r.TableID between myLimitFrom and (myLimitFrom + (myLimitNumber - 1))
 	order by RowNumber;
...
 end $$