0

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.

Ivan
  • 14,692
  • 17
  • 59
  • 96
  • Just monitoring real traffic (for example, MySQL Jet profiler or similar, or just slow-log), before changes for performance. – Moshe L May 24 '12 at 17:11
  • Are you sure that the queries cannot be improved? Perhaps it's a mere case of using better indexes. – eggyal May 24 '12 at 17:11
  • @MosheL the problem is (as always) I have a deadline and if at the end this is not necessary it's a loose of time. I asked it for the chance someone has previous experience on this. – Ivan May 25 '12 at 18:27
  • @eggyal well, normal queries are very fast, but there are a few cross queries (that involve stored procedures) that are really slow. Are not very used, but my tests say that if the number of concurrent users are enough high (50-70) the slow down is remarkable – Ivan May 25 '12 at 18:30
  • MySQL Query cache will work until someone else will modify the tables. If your tables rarely updated - it wil work. else - not. SP can be marked as cacheable or none. – Moshe L May 27 '12 at 17:43

0 Answers0