SQL: Using LIKE statement on a clustered Index

[silent-kill]

---

hi all I'm totally new to SQL optimization....

which do you think is faster to execute

select * from process where batch_ID = 'batch000010000'

or select * from process where ID like 'Proc00001%'

where ID is a clustered index and Batch_ID is just an ordinary field......

[cold_fusion]

If the field on WHERE condition does not have an index, it is slower much, so #2 statement is faster even with % at the end because index checks at the beginning. But if the % is placed at the beginning (LIKE '%00001%'), the results maybe the same.

[jaguar r_e_b_o_r_n]

better use the second one. using an index is a better way!

Good Luck!

[dlanyer77]

bro base on my exposure in SQL tuning. 2nd is preferred but if you really want to fully optimized the performance you need to consider the rest of the statement like for example the "*" and type of index, indexing criteria and so on...

[silent-kill]

ic thx mga bro... ^_^

[abloyboat]

anhi nalang pod ko mangutana ani nga sql. asa ang mas kusog?

1)
select name
from employee
where name like 'A%'

2)
select name
from employee
where substring(name, 1, 1) = 'A'

[cold_fusion]

Definitely #1 if name has an index.

But of both don't have indexes, my guess is still #1 because #2 uses an additional function to get part of a string while #1 uses native SQL which I think is more optimized. The difference will be small.

If you have a large set of data, you could try the two statements and compare the results.

[javapenguin]

anhi nalang pod ko mangutana ani nga sql. asa ang mas kusog?

1)
select name
from employee
where name like 'A%'

2)
select name
from employee
where substring(name, 1, 1) = 'A'

I tested the two in a large table without index in MySQL using MyISAM as table type. My guess is the same as cold fusion but #2 is surprisingly faster.

SELECT count(*) FROM sales1 WHERE itemcode like '048%';
41.24 sec

SELECT count(*) FROM sales1 WHERE substring(itemcode,1,3) = '048';
23.03 sec

I tested another table, but this time substring goes first.

SELECT count(*) FROM sales2 WHERE substring(itemcode,1,3) = '048';
59.18 sec

SELECT count(*) FROM sales2 WHERE itemcode like '048%';
1 min 7.81sec

#2 is still faster, is there a logical explanation to this?

[cold_fusion]

I was wrong. Maybe #2 is difinitely faster.

My only guess that with substring, the whole itemcode string is not retrieved but only 3 chars and compares 3 chars. While with the like % condition, all of itemcode string is retrieved and compared.

I also observed on the 2nd table/comparison, the two has small difference percent-wise compared to the first with the order of test swapped. This is due probably also to hard-disk cache that decreases the time on the 2nd tests. So also basing on the observations, having substring comparison is really faster.

But I think this is only conclusive with MySQL. I am very curious on the results of the above tests using Oracle, MsSQL or PostgreSQL.

[javapenguin]

My only guess that with substring, the whole itemcode string is not retrieved but only 3 chars and compares 3 chars. While with the like % condition, all of itemcode string is retrieved and compared.

Ahh.. Ok, That makes sense.