SQL: Using LIKE statement on a clustered Index

[silent-kill]

---

yeah i think #2 would be faster too.

cuz the first query is going to do a 'X?' on each employee entry

while substring (....) = 'A' is going to match it directly

[dlanyer77]

back to the topic of SQL optimization of Silent-Kill

We are all aware that clusters primary purpose is to improve performance of any queries that has two or more tables but still it has a downside and performance becomes worse when accessing one table individually. Using (select * from process where ID like 'Proc00001%') with Oracle databases is not a good idea. My advice, never use it with Oracle since it is a single table you are accessing but for mySQL it’s a different story because it improves the performance when clustered even if you’re just using a single table. I am also confused with other database clustering behavior. This is a bit confusing right? Welcome to the world of the Infinite Learning! Hehehe!

[abloyboat]

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?

thank you very much for the effort.

thanks all.

[javapenguin]

Your welcome