On the query side, instead of using LIMIT with offset, you can select one more row than you need, and when the user clicks the "next page" link, you can designate that final row as the starting point for the next set of results. For example, if the user viewed a page with rows 101 through 120, you would select row 121 as well; to render the next page, you'd query the server for rows greater than or equal to 121, limit 21.
MySQL performance tip No. 8: Save statistics eagerly, alert reluctantly
Monitoring and alerting are essential, but what happens to the typical monitoring system? It starts sending false positives, and system administrators set up email filtering rules to stop the noise. Soon your monitoring system is completely useless.
I like to think about monitoring in two ways: capturing metrics and alerting. It's very important to capture and save all the metrics you possibly can because you'll be glad to have them when you're trying to figure out what changed in the system. Someday, a strange problem will crop up, and you'll love the ability to point to a graph and show a change in the server's workload.
By contrast, there's a tendency to alert way too much. People often alert on things like the buffer hit ratio or the number of temporary tables created per second. The problem is that there is no good threshold for such a ratio. The right threshold is not only different from server to server, but from hour to hour as your workload changes.
As a result, alert sparingly and only on conditions that indicate a definite, actionable problem. A low buffer hit ratio isn't actionable, nor does it indicate a real issue, but a server that doesn't respond to a connection attempt is an actual problem that needs to be solved.
MySQL performance tip No. 9: Learn the three rules of indexing
Indexing is probably the most misunderstood topic in databases because there are so many ways to get confused about how indexes work and how the server uses them. It takes a lot of effort to really understand what's going on.
Indexes, when properly designed, serve three important purposes in a database server:
If you can design your indexes and queries to exploit these three opportunities, you can make your queries several orders of magnitude faster.
MySQL performance tip No. 10: Leverage the expertise of your peers
Don't try to go it alone. If you're puzzling over a problem and doing what seems logical and sensible to you, that's great. This will work about 19 times out of 20. The other time, you'll go down a rabbit hole that will be very costly and time-consuming, precisely because the solution you're trying seems to make a lot of sense.