Caching results for large multi-table select

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

I'm developing a site that may eventually have a very large database of
users (hopefully, but who knows).  Often there will be queries with multiple
joins and sometimes may include a few fulltext searches.  Each page of
results will have a limited number of results displayed (obviously).  As I
understand it, MySQL caches only exact queries, so doing a LIMIT (x,y) on
each query to get results for the specific page will cause MySQL to run the
entire query again, instead of pulling from the cache.  I will limit the
results to 1000, but what's the best way to limit results per page?

I was thinking that I could run the SELECT and LIMIT to 1000, then to a
mysql_data_seek to jump to the starting row for the page and fetch a certain
number of rows for the page.  For each page, the exact same SELECT would be
used and therefore the results would be cached.

Any other suggestions that might work better?


Site Timeline