LIKE Query Optimization

Posted on

Question :

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?

Answer :

There is no way to optimise the query without making changes to the structure, a LIKE filter like that is always going to result in an index or table scan.

You might get a small improvement with a index over just Order_Description though it would only be a very small difference.

You would be far better to design a proper schema for order details so that you can search by line item properties efficiently, rather than storing it in a single string field. If you add to your question the sort of data you expect to find in that column we might be able to help you in more detail there.

Another option to look into is full text which is available in most SQL Server editions, but I suspect that wouldn’t be an optimal solution here.

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%'

NUTS

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.

SQL Server comes with full text search. If you are searching for strings in the middle of contents on a regular basis then use the full text search facility.

The full text search also allows you to add thesaurus terms to enable you to find terms by alternative names/terms.

It is a bit of effort to get it set up but once done the insertion of records into the underlying table will automatically add the required items to the fulltext index. The fulltext update is not quite realtime but it is considerably faster than having to export data to populate an external search engine.

There are tips and tricks to optimise searching using the fulltext search. Let us suppose you wanted to search descriptions of products but within a category. If your product list is huge it may be worth while having a VARCHAR field that assembles an explicit string such as ProdCat:Footwear. That enables the full text search to satisfy all the search criteria rather than just the free text searching.

There are some additional DB management needs such as how to back up the fulltext index but you would have to learn similar techniques on an external product anyway.

I’ve no experience with SQL Server, but I would put a SOLR alongside my DB, and let that handle the text searches, returning order_id and order_details.id which you can then use for further queries in your DB.

Assuming that in your example (“%Football Size%”) you are searching for whole words (i.e. you’re not looking for text containing “WomensFootball Size” or something of that ilk, but rather “Womens Football Size”), then it sounds to me like a Full Text Index would be worth looking at.

Leave a Reply

Your email address will not be published.