How to fast query serching record where name like 'Rock%'
We had an table let's say employee with only one column name having repetative values Now if I want to search for records that starts with and ending with name say Rock in two diffrent query something as select name from employee where name like 'Rock%'--name starting with Rock select name from employee where name like '%Rock'--name ending with Rock It's obvious that my first query will run faster now my question is how can I make second query faster without changing the execution time of first query
If the Speed is really important then: For the first case build index on the search field. As LIKE 'Roc%' is compatible with index. The second query with '%Rock' will cause full table scan as it is not compatible with indexes. But it is possible to create a persisted calculated `ReversedField` column, which will `REVERSE(Field)`. Then build index on the `ReversedField` and for searching you will use WHERE ReversedField LIKE REVERSE('%Rock') This will be compatible with the index on the reversed column and the speed of searching will be the same as in case of the first query.