sql

[Manuel Paco]

Help please.
I have just two values Yes/No.

What is faster approach?
SELECT * FROM table WHERE smoker = 'No'
or
SELECT * FROM table WHERE smoker LIKE 'N%'
Thank you.

[Avi Abrami]

If you are using an Oracle database, then you can test it yourself very easily (in SQL*Plus). Just time both queries by issuing the command:

set timing on

before running the queries.
[Or am I missing something?]
Hope this helps you.
Good Luck,
Avi.

[unni krishnan]

Originally posted by Manuel Paco:
Help please.
I have just two values Yes/No.

What is faster approach?
SELECT * FROM table WHERE smoker = 'No'
or
SELECT * FROM table WHERE smoker LIKE 'N%'
Thank you.

[unni krishnan]

Originally posted by Manuel Paco:
Help please.
I have just two values Yes/No.

What is faster approach?
SELECT * FROM table WHERE smoker = 'No'
or
SELECT * FROM table WHERE smoker LIKE 'N%'
Thank you.

the secound one is the faster access
thank you

[Avi Abrami]

Unni,
I'd really appreciate it if you would show how you arrived at your conclusion.
Thankyou,
Avi.

[Michael Zalewski]

In general, the LIKE operator will be an order of magnitude slower than an = operator. Also, a LIKE operator will not be able to take advantage of any indexes which may exist on the operand columns.
However, your sample is so simple, you might not be able to find a difference. For example, if you time the two queries in Oracle, I bet the second one (whichever one is run second) will run fastest. That's because the server will have cached all the results from the first query, then the second query only needs to retrieve from the server cache.