LIKE Query Optimization I have the table shown below:

create table Order_Details
(
 ID int not null primary key
,Order_ID int
,Order_Description nvarchar(40)
)


We have inserted 27,500,000 rows in above table, and when I run below query it will takes 6-7 seconds.

select ID,Order_ID, Order_Description 
from Order_Details
where Order_Description  like '%Football Size%'


I have applied a non-clustered index on Order_Description including ID and Order_ID.

Any other ways to optimize query and get result in faster way?
The issue is that double wildcard '%string searches%' are slow in SQL Server because we don't have fancy trigrams searches natively. 

Even with a an index leading on the string search column, we have to read every row.

CREATE NONCLUSTERED INDEX ix_displayname ON dbo.Users(DisplayName)

SELECT u.Id, u.Reputation, u.DisplayName
FROM dbo.Users AS u 
WHERE u.DisplayName LIKE '%Christopher%'




If you're only ever searching for one string (which is never the case), or you have a limited set of known search strings an indexed computed column (or columns) might work in your favor.

ALTER TABLE dbo.Users ADD SearchString 
    AS CONVERT(BIT, CASE WHEN DisplayName LIKE '%Christopher%' THEN 1 ELSE 0 END)

CREATE INDEX ix_yourmom ON dbo.Users (SearchString)


Otherwise, you're gonna have to come up with a better way to locate the data that doesn't involve searching the text, like an OrderLines table which tracks each item in the order that can be searched on an ID.