Query using LIKE

[gxangel]

Hi,

I've read that using LIKE clause in the where query will inhibit index usage on the column.

E.g.

select * from ABC
where id like 'bc12%' and country='US';

If i have the above query and an index on column id, the query will not use the index. To allow index usage, the full id should be typed in the query. However, this will add to the inconvenience of end users as they will have to typed in the full id.

Is there any way which i can reduce the cost of having LIKE query?

Greatly appreciate it if you can show me a link or 2. Thanks in advance!

[davey23uk]

it will use an index if appropriate

[abhaysk]

now where did you read that? (i am sure it will have adjuncts and you may have missed that)

[gxangel]

Do you mean to say that for

select * from ABC
where id like 'bc12%' and country='US';

if i have a query which is long enough e.g. id like 'bc123%' as compared to 'bc%' (when the full id is 'bc12345d'), the query can actually use the index in the first case?

[davey23uk]

it will use the index if appropriate in all cases