Which SELECT is better?

[JJ]

I need to do a SELECT query that joins four tables. The largest of the
tables could eventually have over 1 million records. I need to do a SELECT
that returns a maximum of 1000 records which will display on multiple pages.
Which way would be more efficient....

1. Do the SELECT and LIMIT it to 1000 records, then relying on MySQL's
caching, do a data seek to jump to the record that will begin each page. As
I understand it, MySQL will do the actual query only once and the use the
cache to return the results each time the exact same query is sent to it.

or

2, Do the SELECT and LIMIT it to only the beginning and end records for that
particular page (about 20 records). It is my understanding that MySQL will
only cache the results for that particular page and each time a new page of
records is accessed, MySQL will have to run the entire query again.

So, I'm thinking that option #1 would be more efficient. Even though it's
caching more data, it's only doing the actual query once.

Am I on the right track here or completely off?

Any help would be greatly appreciated. Thanks!

[dayjah]

personally I'd go with two.

make sure your joins are using indexed columns and the frequency
shouldn't be an issue.

My reasoning here is simply that under too many scenarios the query
cache can become highly inaccurate or pointless (java's
PreparedStatement ignores the cache all together).

Mike