Optimised pagination

Hi

I've always been torn between using a COUNT(*) followed by a LIMIT and just doing a single query then using mysql_data_seek(). The two-query solution limits the size of the query result, but becomes very slow when using queries with many joins and/or non-indexed (eg wildcard) searches because you are having to repeat it.

I would be very interested to know if anyone can suggest a more efficient and flexible solution. The ideal would be a way to make MySQL somehow cache the result of the joins etc so that the second query were much faster than the first. But I have no idea how you would do this.