Hi to all,

When trying to query for a certain record, which method is usually faster:

1.) Querying for a record by using a single field in the WHERE clause of your query
Ex: SELECT * FROM Table1 WHERE col1 = 'a';
OR

2.) Querying for a record by adding an AND clause in the WHERE clause of your query
Ex: SELECT * FROM Table1 WHERE col1 = 'a' AND col2 ='b';

Is it right to conclude that method 1 is a bit faster than method 2 because it does not have the AND keyword and does not need to do a comparison?

Thanks and god bless

Nope. What if 90% of the values for col1 are 'a' and col2 is unique?

Could you explain the usefullness of the UNIQUE attribute? How it also affects a certain query?

thanks

What I meant was query 1 would return 90% of the table and query 2 would return between 1 and 0 rows.

if you want a specific record/row then the more you can narrow down the rows returned in the SQL the better. using the SQL engine to do the filtering is going to be far quicker than iterating through all the rows returned to find the row you want.

Why?
the SQL engine is going to be better optimised for that task
you are pumping less data up and down the network
you are doing less work on the client workstation

If you know that only one item say Col1 will give you the row then there is no need to further specify.. but I doubt it will make a significant difference to performance. other things to think of in performance terms are the trade off between number of indexes, which may slow down inserts, edits and deletes OR may speed up fetches of data. If a column is being heavily used in where clauses (or for that matter Join clauses) then it probably should be indexed

I think the assumption that the logical AND calculation would reduce performance stems from normal procedural languages, but here also a very antiquated view, as nowadays this doesn't really come into any effect anymore.