I have a query as follows:

select top (30000) * from table1 where licenseplate like '%as43bv%'

I have an index on licenseplate but the query will not hit the index due to leading wildcard.

Since the column queries license plate numbers full text search wont come to any benefit we are searching on some letter or number rather than words

The above query fetches millions of rows and brings the CPU usage around 100%.

Is there any efficient way to rewrite the query and make use of the indexes.

  • 1
    Just FYSA on your query... the 30,000 records you get back aren't deterministic since you didn't use and order by clause. As long as you are cool with that, then no worries... but if you expect the same rows returned each time you need an order by. Also, why wouldn't a full text index help? – scsimon Oct 31 '19 at 17:12
  • Sorry using order by here but did not mention. My searches are based on either numbers or letters hence i guess full text will be of no benefit. For example '%a%' or '%2%' as i want to fetch plates that has letter 'a' or numbers '12' – SQL_NoExpert Oct 31 '19 at 17:25
  • You should look into it, with CONTAINS – scsimon Oct 31 '19 at 17:43
  • SELECT * sucks, no covering index but the clustered index... – dean Oct 31 '19 at 18:20
  • 1
    Aaron Bertrand had a pretty clever way to make this work. There are obviously some trade offs to make it work, but it works. (And he literally mentions the license plate example) 😁 sqlperformance.com/2017/02/sql-indexes/… – AMtwo Nov 1 '19 at 0:59

What first comes to mind, is that if you are searching for some symbols inside the licenseplate that will always start, for example at 4th symbol (example data 123as43bvxxx ), and it will be always be fixed, you can consider adding computed persisted column to your table

alter table [table1]
add LicenseplateComputed as substring(licenseplate,4,50) persisted

And then create nonclustered index on your newly added computed column "LicenseplateComputed"

After this you will be able to query your table

select top (30000) * from table1 where LicenseplateComputed like 'as43bv%'

and it will perform efficient Index Seek

  • 2
    the thing is that the searches are completely random for example if a state has 6 digit plate the search can start with any letters or numbers '%a%' or '%2%' – SQL_NoExpert Oct 31 '19 at 16:40
  • @SQL_NoExpert then probably search logic must be changed, first letters of license must be supplied instead of trying to search some characters that are in the middle of the column.. sometime we have to zoom out and change approach – Aleksey Vitsko Oct 31 '19 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.