# KeySet Seeker Operation
__*LIMIT clause*__
__*Optimize LIMIT offset operation*__

+ Suppose we have a lot of planets in planets table
+ We want to paginate through it by planet mass in descending order with page size 10
+ Do NOT use `offset`
+ `ALTER TABLE planets ADD INDEX (mass, id);`
+ For example, the first 30 rows and the first 10 rows:
```8

SELECT id, name, mass FROM planets WHERE category_id = 1 ORDER BY mass DESC, id DESC ~!@LIMIT 30@!~;
+-------------+--------------------+-------+
| id          | name               | mass  |
+-------------+--------------------+-------+
~!@|         221 | KWclH2NTy*8{hpH/C? | 99676 |@!~
...
~!@|         191 | `GM{f$&YuH{-|uuU3) | 76997 |@!~
+-------------+--------------------+-------+
30 rows in set (0.00 sec)

SELECT id, name, mass FROM planets WHERE category_id = 1 ORDER BY mass DESC, id DESC ~!@LIMIT 10@!~;
+-------------+--------------------+-------+
| id          | name               | mass  |
+-------------+--------------------+-------+
~!@|         221 | KWclH2NTy*8{hpH/C? | 99676 |@!~
...
~!@| 34234234234 | XXXX               | 90148 |@!~
+-------------+--------------------+-------+
10 rows in set (0.00 sec)
```

------------------------------------------------------------

Note: We will discuss more about index in 201.3 course and in 3xx system administration courses.

Firstly, suppose we have a lot of planets in our planets table.

The first 30 rows by query requirement:

```
tidb> SELECT id, name, mass FROM planets WHERE category_id = 1 ORDER BY mass DESC, id DESC LIMIT 30;
+-------------+--------------------+-------+
| id          | name               | mass  |
+-------------+--------------------+-------+
|         221 | KWclH2NTy*8{hpH/C? | 99676 |
|         385 | 8'.aZ@\bm?@;s]lLEy | 98346 |
|         204 | k?b<_>7-H>P,oX6}YN | 95013 |
|         271 | p@I(2!"7`5C@2Cd5I8 | 94589 |
|          92 | c7{PpeCB7AihEz^W7s | 93088 |
|         359 | h)b$PK>C.+DYN^U+g+ | 92028 |
|          77 | X#Jt8G1M4KL$Zed5{\ | 91188 |
|         255 | X`%s'<P}U7LccG|r2a | 90579 |
|         279 | mB0nCX_@POZ*lP:wDD | 90163 |
| 34234234234 | XXXX               | 90148 |
|         233 | _^tUw]QWZ;x-'uiF'C | 90148 |
|         407 | JSRX.A}v:Ly2C"y2Iz | 88735 |
|         218 | jow),vY[lV|Npeifzp | 87677 |
|          70 | ks)F}Prf6b5_ijDi3{ | 87090 |
|         350 | ]h6ZO)B?Xj>[%U59bw | 86529 |
|          23 | HUyk:-u^,T=NQ5#:rz | 85865 |
|          36 | }O+;&XdG[*-k!:5%bJ | 85735 |
|         420 | k.75WT!A;,$R\3A)hd | 84204 |
|         302 | {g>shkXCW:0_kb@xa9 | 84200 |
|         113 | &EYcK>f(e0S=KeK3qQ | 83163 |
|          37 | .Dbaxc?y}o2U6oJQNO | 82861 |
|         240 | RMZHfwQ3gNc1QK<%;c | 81546 |
|         138 | Ls:r7xlaLI.1ZQp?#( | 81398 |
|         276 | xvQZeP`KSp'yFMA3hp | 81044 |
|         325 | R1TxU0$rT${t_@MB'K | 80053 |
|         366 | W4%lW"P&:1(lFtDC]} | 79181 |
|         219 | 9<#YwJ<fb$<Wmflu(h | 78769 |
|         309 | ldQb9@8xCKiPQpqd8F | 78382 |
|         413 | 7-:"!u_Z#jliEC}F,& | 77287 |
|         191 | `GM{f$&YuH{-|uuU3) | 76997 |
+-------------+--------------------+-------+
30 rows in set (0.00 sec)

tidb> SELECT id, name, mass
    ->   FROM planets
    ->   WHERE category_id = 1
    ->   ORDER BY mass DESC, id DESC
    ->   LIMIT 10;
+-------------+--------------------+-------+
| id          | name               | mass  |
+-------------+--------------------+-------+
|         221 | KWclH2NTy*8{hpH/C? | 99676 |
|         385 | 8'.aZ@\bm?@;s]lLEy | 98346 |
|         204 | k?b<_>7-H>P,oX6}YN | 95013 |
|         271 | p@I(2!"7`5C@2Cd5I8 | 94589 |
|          92 | c7{PpeCB7AihEz^W7s | 93088 |
|         359 | h)b$PK>C.+DYN^U+g+ | 92028 |
|          77 | X#Jt8G1M4KL$Zed5{\ | 91188 |
|         255 | X`%s'<P}U7LccG|r2a | 90579 |
|         279 | mB0nCX_@POZ*lP:wDD | 90163 |
| 34234234234 | XXXX               | 90148 |
+-------------+--------------------+-------+
10 rows in set (0.00 sec)
```

Caution: If your environment does not have as many as dummy planets as the slide shows, reduce the Top-30 to Top-6 and the page size from 10 to 2.