October 26, 2009 at 12:27 pm
I was hoping to get an opinion regarding which search would render results faster...
Our search table is a flat table with over 20 million records.
All fields on the table are indexed as well...
I was wondering if a full-text index search would render results faster on the table search than a simple "=" search, i.e. where columnname = 'value'
In other words, right now, there is a column on the table containing the lastname and first four letters of the first name...when we search, we begin our search by finding the exact match as mentioned above...
Would a full text index search be faster using a "contains" than finding an exact match?
Just wondering what your experiences have been...I believe that there is no faster search than finding exact matches on indexed columns, but i have had limited experience with full text indexes...I am trying to avoid using resources on something that will not render faster results than what is already built.
Thank you all in advance!
October 26, 2009 at 1:28 pm
I admit that I didn’t work with full text search for a while, but from what I remember it won’t help you at all. Full text search is beneficial when you have text and you are looking for specific phrase somewhere in that text. It is meant to prevent you from using wild card in the beginning of the phrase (e.g. use the full text search instead of a query like this – select * from MyTable where MyColumn like ‘%My phrase%’). Since you are not going to use a wild card at all, full text search won’t help you.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 26, 2009 at 1:39 pm
Thank you...that is also my opinion...just wanted some more input!!!
October 27, 2009 at 4:16 am
You can do wildcard "LIKE" searched and still use an index seek - see http://www.databasejournal.com/features/mssql/article.php/3827091/T-SQL-Best-Practices.htm ---> "Speed Up Your Searches by Prefixing Wild Card References".
A little offtopic: I'm not sure if you really need to index all the columns in your table (???)
October 27, 2009 at 7:29 am
Well, unfortunately, this is a table that has 13 different allowable search parameters, with no requirements i.e. must search A AND B...so users can actually search any of the 13 parameters and any combination thereof...I thought indexes on each of the searchable columns was the best route to take...
And yes, we do also do wild card searches, and in that case, full text indexing might surpass the wild card searches...but in the case of the exact matches, I believe there is no way a full text index can surpass the speed of an exact match...
October 28, 2009 at 2:42 pm
Robert Heynen-454800 (10/27/2009)
And yes, we do also do wild card searches, and in that case, full text indexing might surpass the wild card searches...but in the case of the exact matches, I believe there is no way a full text index can surpass the speed of an exact match...
Be aware of SQL Server's limitations on wildcarding in full-text searches. See related thread: http://www.sqlservercentral.com/Forums/FindPost750519.aspx
October 28, 2009 at 2:48 pm
Let me clarify...we do not do wildcard searches in the Full Text Index...
I was merely comparing normal wild card searches to Full Text searches...
Sorry for the confusion...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply