wood burning stoves 2.0*
The moose likes JDBC and the fly likes Regarding time taken by query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Regarding time taken by query" Watch "Regarding time taken by query" New topic
Author

Regarding time taken by query

Priyakant Charokar
Ranch Hand

Joined: May 08, 2004
Posts: 58

There are two tables

TABLE 1
NAME AGE ADDRESS (both are Primary keys)

TABLE 2
NAME AGE ADDRESS (Primary key NAME only)

On which table the below query will execute faster ??

select * from <<TABLE>> where NAME='john' and AGE='18';


SCJP, SCWCD, SCMAD, SCEA, OCPJP 7, CSM
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


NAME AGE ADDRESS (both are Primary keys)

Its not possible to have two primary keys on one entity. Do you mean name and age are a composite key?

Performance should not be a driver for icking primary key candidates. You use them to identify data in a relatable way as a consistant understandable data model. Performance in databases comes later, via query tuning, indices, denormalisation, distribution etc.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

Priyakant,
I'll assume you are talking about indexes rather than keys.

I would expect the two queries to perform similarly. The way to find out for sure is to run a database explain to see how your database does it.

My guess on the execution plan:

table 1
1) use the index to find the one record matching both name and address
2) go to the table row corresponding to that record an return it

table 2
1) use the index to find the one record with a matching name
2) go to the table row corresponding to that record an return it

As you can see, the steps are the same. Table one would take a bit longer because more information is in the index (the extra name column.) Which means less names fit in a single read and the database could have to do more of those to find the right index row.

Note that all this assumes your data set is huge. Otherwise this whole discussion is just an academic exercise anyway.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Priyakant Charokar
Ranch Hand

Joined: May 08, 2004
Posts: 58

Thanks all, actualy I was talking about index.

Thanks again.
 
wood burning stoves
 
subject: Regarding time taken by query
 
Similar Threads
javaranch mock question
Pagebreak in JSP Page
SQL Question
XPath Query
Exception:Attempt to get a lock for a null object ?