If you want it to work quicker, use 'text%' instead of '%text%'. Of course, that may not do what you want.
As a general rule, indexes are rather useless for %text% wildcards. Think about how indexes work. If you use the index at the back of a book to locate a page, you are relying on a sorted index (a comes before b which comes before c and so on) to help you locate the matches. But if you wanted to find index entries for words that had "a" in the middle, well, the index would be useless, you'd need to read every entry to find the matches. And database indexes are much the same. The preferred way to do freeform searching is to use a fulltext search engine, be it one built in to the database or an external one (like Verity in CF). --- Ben -----Original Message----- From: Peterson, Andrew S. [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 07, 2005 11:35 AM To: CF-Talk Subject: OT: SQL Server Index Won't 'Take' Hi, I placed a clustered index on field strName in a table of 800,000 records. I run a query with a LIKE clause on a wildcard and it takes 20 seconds to return a result. I remove the index and it takes the exact same amount of time. In other words, adding the index on the searched field does nothing to performance on the following select statement: SELECT strName FROM table WHERE strName like '%Accenture%' Any ideas why adding an index will not speed up the query? Any overall performance hints? I've tried a view with a group by which dropped the rowcount to 180 K, but got the same response time. Thanks in advance for any assistance. Sincerely, Andrew ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226445 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
