Is there any way to improve performance on like searches that have the % to the left of the searched value?

Question

I have a query that does a like statement on a column that stores fullpath locations of files on a computer.

Example

select * 
from table 
where fullpath like '%hi.exe'

Which never seems to use an index and is very slow compared to doing a fullpath = 'value' (obviously)

But my question is, is there any way or ideas on how to speed up query results or get it to use indexes?

I dont see the same slowness if the like is on the right side (ex select * from table where fullpath like 'hi%'

EDIT: Microsoft Sql server 2017 Standard Edition

Answer 1

FWIW, I don't think the previously provided duplicate questions were actually duplicate of this specific question and instead answered a more complex tangentially related question on how to improve searching with double wildcards (contains type of searches). Some of the answers probably can be applied here as well, but are overly complicated for this much easier problem, hence my vote to re-open the question. I tried locating a suitable duplicate with the simpler solution but could not find one.

---

Is there any way to improve performance on like searches that have the % to the left of the searched value?

Assuming you do strictly mean only starts-with type of searches (only leading wildcards) and not contains type of searches, then there are some fairly simple solutions.

One solution involves storing a copy of the column reversed (a computed column can help here) and then creating an index on the reversed column. Then you can reverse your wildcard order to make it an ends-with search against the reversed column. This would be logically equivalent and also be sargable, therefore allowing the index to be used in an efficient manner.

Example code:

-- Step 1: Add a computed column with the reversed column
ALTER TABLE YourTable
ADD fullpath_reversed AS REVERSE(fullpath);

-- Step 2: Index the newly created reversed column 
CREATE NONCLUSTERED INDEX IX_YourTable_fullpath_reversed ON YourTable (fullpath_reversed);

-- Step 3: Query the newly created column efficiently 
select * 
from YourTable 
where fullpath_reversed like 'exe.ih%';

---

Note you shouldn't use SELECT * in any non-adhoc code, especially when you're concerned about performance. Only list out the columns you actually care about for that particular query. It's more performant both from reading less data than necessary and potentially resulting in a better query plan, with indexes chosen, and index operations used.

Answer 2

If you typically search for file names, store them separately instead of embedded in fullpath.

Might be better to store path, file name, and maybe extension separately depending on the queries.

Will use less space than a reversed copy of the fullpath.

Answer 3

Maintain a separate column with less width for filename only.Also if you multiple file extension type then you can maintain tinyint flag in separate column.

It is only one time task during insert/update proc.

This way you can avoid Like operator altogether.

SARGable is only one of the factor for optimizer for using Index

Another main factor is column width.Less the column width,there will be less number of index page.So optimizer will have to read less page,so more the chance of using Index.

Optimizer often make good enough plan and cost effective plan.In some case even if Index is not use but still plan is good enough.

Do not make Index on column of datatype varchar with more width.If table contain million of rows later then this index get wasted and might take longer to execute even small query which return less number of rows