Menu
We have a whole bunch of queries that "search" for clients, customers, etc. You can search by first name, email, etc. We're using LIKE statements in the following manner:
SELECT * FROM customer WHERE fname LIKE '%someName%' Does full-text indexing help in the scenario? We're using SQL Server 2005.
687
Full-text indexes are created on text-based columns ( CHAR , VARCHAR , or TEXT columns) to speed up queries and DML operations on data contained within those columns. A full-text index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX .
Full-text search is applicable in a wide range of business scenarios such as e-businesses—searching for items on a web site; law firms—searching for case histories in a legal-data repository; or human resources departments—matching job descriptions with stored resumes.
Indexes are a special kind of lookup table that is used by the database search engine to speed up data retrieval from the tables. Basically, an index is a pointer that points to the tuples of a table. An index in a database is very similar to an index in the back of a book.
View the properties of a full-text index with Transact-SQL Returns a row for each full-text catalog to full-text index reference. Contains a row for each column that is part of a full-text index. A fulltext index uses internal tables called full-text index fragments to store the inverted index data.
It will depend upon your DBMS. I believe that most systems will not take advantage of the full-text index unless you use the full-text functions. (e.g. MATCH/AGAINST in mySQL or FREETEXT/CONTAINS in MS SQL)
Here is two good articles on when, why, and how to use full-text indexing in SQL Server:
173
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
