mysql pagination to display a page with a specific row in it

Now I have a question on mysql paging. A user's record is displayed on a table with many other user's record. an开发者_JAVA技巧d the table is sorted/paged. Now i need to display the page that containing the user's row directly after the user login. How can I achieve this? A simple thought would be firstly find out the rownum of the user, then do the paging accordingly, but I'm wondering if there are better ways to do it. thanks.

Sample table for discussion

create table t_users (id int auto_increment primary key, username varchar(100));
insert t_users(username) values
('jim'),('bob'),('john'),('tim'),('tom'),
('mary'),('elise'),('karl'),('karla'),('bob'),
('jack'),('jacky'),('jon'),('tobias'),('peter');

The query to show the page on which the user is on, not specifically putting the user to the top of the page (which would have been a lot easier)

select username, id
from
  (select count(*) pos
   from t_users
   where username <= 'tobias') pos,
  (select @row:=@row+1 row, u.*
   from (select @row:=0) initvars, t_users u
   order by u.username, u.id) numbered
where floor((numbered.row + 3)/4) = floor((pos.pos+3)/4);

Notes:

1. The page size here is 4, the number 3 is the result of taking 1 off the page size
2. The username of the user who just logged in is 'tobias', which is used in the first sub-query
3. There had better be an index along the ORDER BY clause (in this case on username)
4. This will cause a table scan to fully row-number the last subquery

select count(*) 
FROM table 
WHERE [sort_by_field] < [actual_records_value]