SQL Server: Index columns used in like?

Question

Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query:

SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%'

I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzing if a query has been optimized with an index?

This is very problematic when searching large tables with a data tables type scenario where all coloumns need to do a .contains search — Zapnologica, Jun 06 '18 at 11:03

score 101 · Accepted Answer · edited Feb 21 '18 at 16:11

101

Given the data 'abcdefg'

WHERE Column1 LIKE '%cde%'  --can't use an index

WHERE Column1 LIKE 'abc%' --can use an index

WHERE Column1 Like '%defg' --can't use an index, but see note below

Note: If you have important queries that require '%defg', you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on:

WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index

edited Feb 21 '18 at 16:11

Dan Esparza

28,047
29
99
127

answered Sep 08 '09 at 19:32

KM.

101,727
34
178
212

5

I like your solution, though I'd call it an ugly hack ;) – idstam Sep 09 '09 at 19:35
14

I've used this ugly hack on a system where the original designer made numbers char(x) with leading zeros and other keys letter+ leading zeros. try telling users they have to enter in the leading zeros and or letter+ leading zeros! ha. they want to enter in 203, not G000000203, so this reverse() "hack" will help there. – KM. Sep 09 '09 at 20:14
@KM. referenced this answer here: http://dba.stackexchange.com/questions/91340/1-hour-query-is-there-another-way-to-do-this/91346#91346 – DForck42 Feb 06 '15 at 21:17
2

I wouldn't call it a hack but a very nice solution. – yakya Aug 11 '17 at 08:00
21

It's a **beautiful** ugly hack – Reversed Engineer Jun 15 '18 at 13:17
Not working like me expect. Ex: I want search "have" in "I have a machine". And not working – HOÀNG LONG Mar 27 '21 at 05:43

score 15 · Answer 2 · answered Sep 07 '09 at 07:47

15

In my experience the first %-sign will make any index useless, but one at the end will use the index.

answered Sep 07 '09 at 07:47

idstam

2,848
1
21
30

Close enough - LIKE will use the prefix on an index but you can't resolve a wildcard through an index so that's as far as it will go. – ConcernedOfTunbridgeWells Sep 07 '09 at 08:00
So yay or nay for indexes on these columns? – Niels Bosma Sep 07 '09 at 08:02
3

If you always query the column with a leading %, the index will never be used. The answer thus depends on how you query your data. – Lieven Keersmaekers Sep 07 '09 at 08:43
**NOT COMPLETELY TRUE**, you _CAN_ hit an index on queries that use _LIKE '%xyz'_, but you need to do a little extra work, see my answer for an explanation – KM. Sep 09 '09 at 13:00

MicSim · Answer 3 · 2018-07-18T08:13:04.143

To answer the metrics part of your question: The type of index/table scan/seek being performed is a good indicator for knowing if an index is being (properly) used. It's usually shown topmost in the query plan analyzer.

The following scan/seek types are sorted from worst (top) to best (bottom):

Table Scan
Clustered Index Scan
Index Scan
Clustered Index Seek
Index Seek

As a rule of thumb, you would normally try to get seeks over scans whenever possible. As always, there are exceptions depending on table size, queried columns, etc. I recommend doing a search on StackOverflow for "scan seek index", and you'll get a lot of good information about this subject.

SQL Server: Index columns used in like?

3 Answers3

Linked