470,580 Members | 2,345 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,580 developers. It's quick & easy.

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!
May 18 '06 #1
1 1166
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

May 26 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by John | last post: by
3 posts views Thread by Marco | last post: by
3 posts views Thread by Dennis M. Marks | last post: by
3 posts views Thread by Srini | last post: by
7 posts views Thread by Lauren Quantrell | last post: by
15 posts views Thread by Rob Meade | last post: by
3 posts views Thread by =?Utf-8?B?Um9sYW5kcGlzaA==?= | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.