Menu
Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query:
SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%' I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzing if a query has been optimized with an index?
791
Indexes cannot be used with LIKE '%text%' predicates. They can, however with LIKE 'text%'.
Primary key columns are typically great for indexing because they are unique and are often used to lookup rows.
Check if the user seeks of the index are increasing with every select on the table u r using unless the server is restarted this will give you a clear idea which index is being used or not.
Included columns can be used to create a covering indexes without including all the data into the key columns. This covering index has the advantage that the index contains all the columns that are needed for a query.
Given the data 'abcdefg'
WHERE Column1 LIKE '%cde%' --can't use an index WHERE Column1 LIKE 'abc%' --can use an index WHERE Column1 Like '%defg' --can't use an index, but see note below Note: If you have important queries that require '%defg', you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on:
WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
