473,395 Members | 1,532 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 1262
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: John | last post by:
I have over 5000 thumbnail pictures of size 5kb each. I would like to able to load all 5000 pictures and view 50 per page using mysql_data_seek(). I would like to know what are the advantages and...
3
by: Marco | last post by:
Currently I have 3 mysql tables with about 6 columns each, sometimes I have to access to all of them at the same time but most of the times I access 2 at the same time. I was wondering which is...
3
by: Dennis M. Marks | last post by:
I have a problem with the following code. It generates a <FORM><SELECT><OPTION> list. There is no problem in the generating. The problem is in the execution as follows. It works fine in Mac IE...
3
by: Srini | last post by:
Hello, I would like to know Identity OR sequence which is better?. My requirement is simple. I have a parent table with ID (generated or from sequence) and I want that ID after inserting...
7
by: Lauren Quantrell | last post by:
Is there any speed/resource advantage/disadvantage in using Select Case x Case 1 Case 2 etc. many more cases... End Select VS.
4
by: Hai Nguyen | last post by:
I'm learning C sharp and do not like vb much. I'm creatiing a wepage using panel to test myself. I tried to use these code below, which is written in VB, and to transform them to c sharp but I got...
5
by: Peggy Wu | last post by:
dear all, I am a programmer ,coding in IBM COBOL and the DB is DB2 V6 on OS/390. As below, for the performance issue, Does anyone know which one is better ?? To get a record that field A has a...
15
by: Rob Meade | last post by:
Hi all, I have a databse which I'm pulling the data from for my ASP page. I have 4 tables, Course, Feature, Objective, and PreRequisite. The last three all contain a course product code and a...
3
by: =?Utf-8?B?Um9sYW5kcGlzaA==?= | last post by:
Hi, I'm doing an application using C# and I have this question: I have a method called sqlQueryBD which receives a string sql query and executes it against a database. I also have a class called...
2
theGeek
by: theGeek | last post by:
I always wonder which one of join or subquery should I be using to solve a particuar problem so that I get the correct result set in minimum time. It usually happens that I can write a query quickly...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.