Recommended way to do paging in mysql when count matters

I have a MySQl suggestion table like this

id:int
user_id:int
item_id:int

and I wanted to implement paging for the most suggested items by users. assume each page size is 9. so i have tried

   select count(id) as total,item_id from suggestions group by item_id order by total desc limit 9;

which simply works for the first page and returns

+-------+---------+
| total | item_id |
+-------+---------+
|     6 |       4 |
|     6 |       8 |
|     6 |      16 |
|     5 |       5 |
|     5 |       6 |
|     5 |       1 |
|     3 |      17 |
|     2 |       3 |
|     2 |       7 |
+-------+---------+

in second page i wanted to show the results from total = 2 and item_id =2. In SQL server i can use Row_number() method to number the rows for paging. But i can't find something like this in MySQL.

am new to mysql, can somebody help me out.

see the attached the sample data below

+----+---------+---------+---------------------+---------------------+
| id | user_id | item_id | created_at          | updated_at          |
+----+---------+---------+---------------------+---------------------+
|  1 |       4 |       1 | 2011-04-18 05:21:08 | 2011-04-18 05:21:08 |
|  6 |       5 |       2 | 2011-04-18 07:28:25 | 2011-04-18 07:28:25 |
|  8 |       5 |       3 | 2011-04-18 08:10:46 | 2011-04-18 08:10:46 |
|  9 |       4 |       4 | 2011-04-18 12:58:17 | 2011-04-18 12:58:17 |
| 10 |       4 |       5 | 2011-04-18 13:24:59 | 2011-04-18 13:24:59 |
| 11 |       4 |       6 | 2011-04-18 13:28:28 | 2011-04-18 13:28:28 |
| 12 |       4 |       7 | 2011-04-18 13:30:20 | 2011-04-18 13:30:20 |
| 13 |       8 |       5 | 2011-04-18 13:42:30 | 2011-04-18 13:42:30 |
| 14 |       8 |       6 | 2011-04-18 13:42:44 | 2011-04-18 13:42:44 |
| 15 |       8 |       4 | 2011-04-18 13:42:52 | 2011-04-18 13:42:52 |
| 16 |       9 |       5 | 2011-04-18 13:44:34 | 2011-04-18 13:44:34 |
| 17 |       9 |       6 | 2011-04-18 13:45:23 | 2011-04-18 13:45:23 |
| 18 |       9 |       7 | 2011-04-18 13:46:28 | 2011-04-18 13:46:28 |
| 19 |       9 |       1 | 2011-04-18 13:46:29 | 2011-04-18 13:46:29 |
| 20 |       4 |       8 | 2011-04-18 13:58:38 | 2011-04-18 13:58:38 |
| 21 |       8 |       8 | 2011-04-18 13:59:39 | 2011-04-18 13:59:39 |
| 22 |       9 |       8 | 2011-04-18 14:00:47 | 2011-04-18 14:00:47 |
| 23 |       8 |       1 | 2011-04-18 14:03:58 | 2011-04-18 14:03:58 |
| 24 |       4 |       9 | 2011-04-19 10:24:38 | 2011-04-19 10:24:38 |
| 25 |       4 |      10 | 2011-04-19 10:54:13 | 2011-04-19 10:54:13 |
| 26 |       4 |      11 | 2011-04-19 10:57:10 | 2011-04-19 10:57:10 |
| 27 |      12 |       8 | 2011-04-19 11:33:02 | 2011-04-19 11:33:02 |
| 28 |      12 |       4 | 2011-04-19 11:33:18 | 2011-04-19 11:33:18 |
| 29 |      12 |       1 | 2011-04-19 12:07:51 | 2011-04-19 12:07:51 |
| 30 |      12 |       6 | 2011-04-19 12:09:05 | 2011-04-19 12:09:05 |
| 31 |      12 |      12 | 2011-04-19 13:07:35 | 2011-04-19 13:07:35 |
| 32 |       9 |      13 | 2011-04-19 13:29:17 | 2011-04-19 13:29:17 |
| 33 |       9 |      14 | 2011-04-19 13:45:56 | 2011-04-19 13:45:56 |
| 34 |       9 |      15 | 2011-04-19 13:49:08 | 2011-04-19 13:49:08 |
| 35 |      14 |      16 | 2011-04-19 16:47:28 | 2011-04-19 16:47:28 |
| 36 |       4 |      16 | 2011-04-19 16:49:46 | 2011-04-19 16:49:46 |
| 37 |      13 |      16 | 2011-04-19 16:50:29 | 2011-04-19 16:50:29 |
| 38 |      15 |       5 | 2011-04-19 16:53:55 | 2011-04-19 16:53:55 |
| 39 |      15 |      16 | 2011-04-19 16:54:15 | 2011-04-19 16:54:15 |
| 40 |      15 |       8 | 2011-04-19 16:54:29 | 2011-04-19 16:54:29 |
| 41 |      15 |       4 | 2011-04-19 16:58:20 | 2011-04-19 16:58:20 |
| 42 |      15 |       0 | 2011-04-19 16:59:21 | 2011-04-19 16:59:21 |
| 44 |       9 |       4开发者_运维知识库 | 2011-04-19 17:03:54 | 2011-04-19 17:03:54 |
| 45 |       9 |      16 | 2011-04-19 17:03:57 | 2011-04-19 17:03:57 |
| 46 |      13 |       8 | 2011-04-19 17:08:56 | 2011-04-19 17:08:56 |
| 47 |      13 |       6 | 2011-04-19 17:08:58 | 2011-04-19 17:08:58 |
| 48 |      13 |      17 | 2011-04-19 17:10:26 | 2011-04-19 17:10:26 |
| 49 |      18 |      16 | 2011-04-19 17:15:49 | 2011-04-19 17:15:49 |
| 50 |      18 |       4 | 2011-04-19 17:15:52 | 2011-04-19 17:15:52 |
| 51 |      18 |       1 | 2011-04-19 17:15:53 | 2011-04-19 17:15:53 |
| 52 |      18 |       5 | 2011-04-19 17:15:54 | 2011-04-19 17:15:54 |
| 53 |      18 |      17 | 2011-04-19 17:15:57 | 2011-04-19 17:15:57 |
| 54 |      19 |      17 | 2011-04-19 17:21:45 | 2011-04-19 17:21:45 |
| 55 |      19 |      18 | 2011-04-19 17:22:28 | 2011-04-19 17:22:28 |
| 56 |      22 |      19 | 2011-04-19 17:35:40 | 2011-04-19 17:35:40 |
| 57 |      26 |      20 | 2011-04-19 17:46:00 | 2011-04-19 17:46:00 |
| 58 |      26 |      21 | 2011-04-19 17:51:51 | 2011-04-19 17:51:51 |
| 59 |      26 |      22 | 2011-04-19 17:54:32 | 2011-04-19 17:54:32 |
| 60 |      26 |      23 | 2011-04-19 18:11:24 | 2011-04-19 18:11:24 |
| 61 |      26 |      24 | 2011-04-19 18:12:23 | 2011-04-19 18:12:23 |
| 62 |      27 |       2 | 2011-04-19 18:24:38 | 2011-04-19 18:24:38 |
| 63 |      27 |      25 | 2011-04-19 18:29:07 | 2011-04-19 18:29:07 |
| 64 |      27 |      26 | 2011-04-19 18:31:51 | 2011-04-19 18:31:51 |
| 65 |      27 |       3 | 2011-04-19 18:32:46 | 2011-04-19 18:32:46 |
+----+---------+---------+---------------------+---------------------+

Have you tried:

select count(id) as total,item_id from suggestions 
group by item_id order by total desc 
limit 9 offset 9;