I know that both Wild Cards and Functions must be avoided in the WHERE clauses if possible. My question is: which one is yet better in terms of performance, assuming that we have to choose between one of them? More specifically, I have the following two queries and I want to know which type of query is preferred in general:
select FirstName from person.person where FirstName like 'S%'
select FirstName from person.person where LEFT(FirstName,1) = 'S'
/****** Object: Index [IX_Person_LastName_FirstName_MiddleName] Script Date: 11/30/2016 12:53:05 PM ******/
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Both queries are doing index scan so those are OK. But we dont know how you setup your index or table sizes.
First version can use a basic index on firstName because the
% is at the end.
FirstName like 'S%' FirstName Like '%S%' -- cant use index
Second Version cant use the index on firstName, but you can create a calculated index for
LEFT(FirstName,1) that is why you should show us your index.
This is article have the best tips to create index. MySQL index TIPS