0

My query contains LIKE "%BLABLA". Will it optimize my query if I add an index? If yes, will both clustered and unclustered B+Tree indexing improve my query?

Thanks!

Codevan
  • 538
  • 3
  • 20
  • Tag the dbms you're using. This is a product specific question! – jarlh Sep 16 '16 at 12:17
  • 1
    I don't think a `LIKE` filter will be able to use anything coming after the wildcard during tree traversal. So therefore I don't think an index would help in this particular query. – Tim Biegeleisen Sep 16 '16 at 12:19
  • @TimBiegeleisen but here:http://stackoverflow.com/questions/543580/equals-vs-like/39531185#39531185 they say it is possible for any other LIKE expressions – Codevan Sep 16 '16 at 12:22
  • @Codevan Welcome to Stack Overflow. I'm not sure that link is relevant. The equals operator would not work for the query in the OP, so comparing it to `LIKE` is irrelevant. – Tim Biegeleisen Sep 16 '16 at 12:26

2 Answers2

1

LIKE 'abc' is very similar to = 'abc';
LIKE 'abc%' is a "range", which is likely to be able to us an index;
LIKE '%abc' cannot use an index - because of the leading wildcard.

If you would like to discuss what could be done for your query, please provide the entire query, plus SHOW CREATE TABLE.

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

You can store the reversed data in another field, add an index on that reversed field and query against that reversed field. Instead of using LIKE '%abc' you will use LIKE 'cba%', using the reversed field.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
naresh
  • 2,113
  • 20
  • 32