My query needs to do a LIKE search over 3 columns.
SELECT * FROM Monkeys
WHERE [Name] LIKE '%pete%' OR [Desc] LIKE '%pete%' OR [Info] LIKE '%pete%';
I am looking to improve the performance of this query.
I can't use full-text catalogs, just simple tables. There are about 200,000 rows (SQL Server 2008 database) and it takes 3 to 6 seconds.
Doe anyone have any tips?
Full text indexing/search if you have leading wildcards.
No amount of normal indexing will help you to make it fast, sorry, whether multiple or single columns with wildcards.
Your best least worst chance is to have composite index on the 3 columns and hope the optimizer scans this rather a table scan, but frankly I've not tried it myself. And likely you can't because index key column combined width has to be less than 900 bytes