While MySQL is pretty good as cacheing, some of our tables (and some of our users tables) are getting pretty big and clearly there will more fighting for memory. Our main big queries are those that wil involve paging when displayed.

We currently run the same query over and over again for different pages with a LIMIT clause. Again MySQL is pretty good at this but is there a better way?

One suggestion I would like to try is that when we first run such a query we first collect the keys. That might be all the topics in a forum or all the posts in a topic. If the number of keys shows that we are going to require paging we save the keylist in a 'transient' table record.

We then use the key list t identify which records we need for the page and then run the main query but this time capped by the keylist.

When the request comes through for the next page we grab the keylist and perform the same operation.

the good bit is that the keylist can stay there so that if another user runs the same forum/topic we do not have to recreate the keylist.

We remove the keylist when a new topic is added to the forum or a new post is added to the topic.

Clearly something like this would need testing to determine performance but instinct suggests that on a query that might retiurn many hundreds of rows this could be a winner.

Thought anyone?