Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Regarding time taken by query

 
Priyakant Charokar
Ranch Hand
Posts: 58
Hibernate Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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';
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Priyakant Charokar
Ranch Hand
Posts: 58
Hibernate Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks all, actualy I was talking about index.

Thanks again.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic