LIKE vs EQUAL

Nileshkul · New User Joined: 09 May 2016 Posts: 43 Location: India

Dear all,

I have a query which uses LIKE in WHERE clause for 4 character comparision.
Some thing like:
Where column1 LIKE :HOST1
Here character 2 to 4 are populated with _ underscore as wild char based on user input.
But many times - all 4 character are populated propely and nit wild so will use of EQUAL instead LIKE give better result.
Thanks.

Nileshkul · New User Joined: 09 May 2016 Posts: 43 Location: India

Just to be clear - I want to know if equal to perform better than Like given that there is no wild card in Like host variable.
By performance, I mean whether Equal to will take less CPU than Like without wild card characters

Nic Clouston · Posted: Sat Jul 16, 2016 5:13 pm

What happened when you tried a simple test?

Rohit Umarjikar · Posted: Mon Jul 18, 2016 11:09 am

First find the length of the field , if it 4 then execute with equal operator else with like.They both works differently so though like is good to avoid but you are out of choice.

Nic Clouston · Posted: Mon Jul 18, 2016 11:57 am

If the column is indexed and you have the full key then do not use LIKE.

Pandora-Box · Posted: Mon Jul 18, 2016 1:27 pm

I reiterate what Nic has said you might need to validate if the HOST1 is going to be filled with the key or with wild card based on which you might need to execute the query accordingly

Nileshkul · New User Joined: 09 May 2016 Posts: 43 Location: India

Thanks all for replies.
The column is not key & I tried simple test, CPU uti
lization is almost same.
Thanks again, very helpful

Rohit Umarjikar · Posted: Tue Jul 19, 2016 8:17 am

It must have to be indexed.you will have to try against the actual production data and not with the test data.