A Light-hearted Look at Pagination Optimization

When I was browsing through my database using pagination, I assumed using LIMIT and OFFSET would neatly slice the data into the exact portion I wanted. Turns out, I was a bit off the mark.

It took me on a trip down memory lane to my college days, where I learned about databases. Remembering those lessons, I realized that without specific indexing, databases are like old-school cassettes; you have to play through the entire thing to find your favorite song.

So, what's the smart move for speeding up database queries during pagination? I dug into this and found some cool insights in the official MariaDB documentation.

Spotting the Snag

Picture this: a giant database closet with 50,000 coats. Now, if you want to browse them 10 at a time, it sounds simple, right? But here’s what really happens with a typical SELECT ... OFFSET 49990 LIMIT 10 command:

1. It rummages through all 50,000 coats.
2. Ignores the first 49,990.
3. Shows you the last 10.

Imagine doing this 5,000 times! That's like checking every coat in the closet over and over to find a new set of 10. Massive time-sink and a real drag for users.

More Hiccups

It's not just about the slog. There are other gremlins in the works:

• Say goodbye to consistent results if someone sneaks a coat in or out while you're browsing.
• Forget bookmarking pages; they shift faster than fashion trends.
• Using WHERE and ORDER BY together? You're still stuck flipping through the whole closet for just those 10 special coats on the first shelf.

Turning the Page (Literally)

Here's a lightbulb moment: Ditch the 'OFFSET'! Instead of counting from the start each time, just note where you left off.

For the window shoppers (viewing the first 10 items):
    SELECT ... WHERE ... ORDER BY id DESC LIMIT 10
For the second round (next 10 items):
    SELECT ... WHERE ... AND id < $where_I_left_off ORDER BY id DESC LIMIT 10

Kicking OFFSET to the Curb

Usually, pagination URLs look like a secret code:
?topic=xyz&page=4999&limit=10

But why stick with OFFSET when it’s just a 'page times limit' in disguise?

Let's mix it up: ?topic=xyz&id=12345&limit=10. We're swapping 'page' with 'id'. It's like choosing a specific coat to start from instead of counting from the front every time.

WHERE topic = 'xyz'
    AND id >= 12345
ORDER BY id
LIMIT 10

Now, we're only eyeing the 10 coats we actually want to see.

The Next Page Dilemma

What if we hit the end and there are only 10 coats left? One option is to just hide the 'Next' button. But we can be slicker by asking for 11 coats (LIMIT 11) instead of 10. This way, we know if there's a next page and also get the starting point for it, all in one go.

Connecting the Dots for Future Pages

Want to leap over several pages in one go? Here’s how:

1. Use LIMIT 51 to peek at the coats for the next five pages.
2. Split it up: First 10 coats for now, rest for later page links.

By tweaking our approach, we can turn a mundane task into a snappy, more enjoyable experience. Not just for us, but for our database too :)