I've developed an on-demand data system similar (in appearance) to that Facebook has, it is, when you arrive to the bottom of the page, there is an AJAX request to load more data.
The "problem" is that some of the mySQL queries that this on-demand system runs are relatively slow. So, to prevent scalability problems, I've designed (not implemented yet) a cache system (used only in this on-demand cases) that stores the results of the query:
SELECT * FROM table WHERE ....
when the user (the app) asks for
SELECT * FROM table WHERE .... LIMIT 0,9
so, when the user scrolls down and the AJAX trigger asks mySQL for the next results:
SELECT * FROM table WHERE .... LIMIT 10,19
the cache system will offer the results without having to make another query to mySQL.
The question is: is all this stuff necessary? Or the default mySQL cache system is enough? In other words, I know that mySQL cache systems are able to return the right results without search again if you run the same query in a short lapse of time, but are so "smart" to avoid search when is appended to the same query a different LIMIT?
Of course, if you know a better way to accelerate this, please let me know it.