Regarding time taken by query

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';

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.

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.

Thanks all, actualy I was talking about index.

Thanks again.