<p>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:  </p>

<pre><code>SELECT * 
FROM customer 
WHERE fname LIKE '%someName%'
</code></pre>

<p>Does full-text indexing help in the scenario? We're using SQL Server 2005.</p>

[{'answer_id': 57930, 'author': 'Prestaul', 'author_id': 5628, 'author_profile': 'https://Stackoverflow.com/users/5628', 'pm_score': 6, 'selected': True, 'text': '<p>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. <a href="http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html" rel="nofollow noreferrer">MATCH/AGAINST</a> in mySQL or FREETEXT/CONTAINS in MS SQL)</p>\n<p>Here is two good articles on when, why, and how to use full-text indexing in SQL Server:</p>\n<ol>\n<li><a href="https://www.developer.com/database/sql-server-full-text-searching/" rel="nofollow noreferrer">How To Use SQL Server Full-Text Searching</a></li>\n<li><a href="https://www.developer.com/guides/solving-complex-sql-problems-with-full-text-indexing/" rel="nofollow noreferrer">Solving Complex SQL Problems with Full-Text Indexing</a></li>\n</ol>\n'}, {'answer_id': 57971, 'author': 'Brannon', 'author_id': 5745, 'author_profile': 'https://Stackoverflow.com/users/5745', 'pm_score': 2, 'selected': False, 'text': "<p>To answer the question specifically for MSSQL, full-text indexing will <strong>NOT</strong> help in your scenario.</p>\n\n<p>In order to improve that query you could do one of the following:</p>\n\n<ol>\n<li>Configure a full-text catalog on the column and use the CONTAINS() function.</li>\n<li><p>If you were primarily searching with a prefix (i.e. matching from the start of the name), you could change the predicate to the following and create an index over the column.</p>\n\n<p>where fname like 'prefix%'</p></li>\n</ol>\n\n<p>(1) is probably overkill for this, unless the performance of the query is a big problem.  </p>\n"}, {'answer_id': 58176, 'author': 'Josef', 'author_id': 5581, 'author_profile': 'https://Stackoverflow.com/users/5581', 'pm_score': 5, 'selected': False, 'text': "<p>FTS <em>can</em> help in this scenario, the question is whether it is worth it or not.</p>\n\n<p>To begin with, let's look at why <code>LIKE</code> may not be the most effective search. When you use <code>LIKE</code>, especially when you are searching with a <code>%</code> at the beginning of your comparison, SQL Server needs to perform both a table scan of every single row <em>and</em> a byte by byte check of the column you are checking.</p>\n\n<p>FTS has some better algorithms for matching data as does some better statistics on variations of names. Therefore FTS can provide better performance for matching Smith, Smythe, Smithers, etc when you look for Smith.</p>\n\n<p>It is, however, a bit more complex to use FTS, as you'll need to master <code>CONTAINS</code> vs <code>FREETEXT</code> and the arcane format of the search. However, if you want to do a search where either FName or LName match, you can do that with one statement instead of an OR.</p>\n\n<p>To determine if FTS is going to be effective, determine how much data you have. I use FTS on a database of several hundred million rows and that's a real benefit over searching with <code>LIKE</code>, but I don't use it on every table.</p>\n\n<p>If your table size is more reasonable, less than a few million, you can get similar speed by creating an index for each column that you're going to be searching on and SQL Server should perform an index scan rather than a table scan.</p>\n"}, {'answer_id': 17368074, 'author': 'Strinder', 'author_id': 752385, 'author_profile': 'https://Stackoverflow.com/users/752385', 'pm_score': 3, 'selected': False, 'text': '<p>According to my test scenario:</p>\n\n<ul>\n<li>SQL Server 2008</li>\n<li>10.000.000 rows each with a string like "wordA wordB\nwordC..." (varies between 1 and 30 words)</li>\n<li>selecting count(*) with CONTAINS(column, "wordB")</li>\n<li>result size several hundred thousands</li>\n<li>catalog size approx 1.8GB</li>\n</ul>\n\n<p>Full-text index was in range of 2s whereas <em>like \'% wordB %\'</em> was in range of 1-2 minutes.</p>\n\n<p><strong>But this counts only if you don\'t use any additional selection criteria!</strong> E.g. if I used some <em>"like \'prefix%\'"</em> on a primary key column additionally, the performance was worse since the operation of going into the full-text index costs more than doing a string search in some fields (as long those are not too much). </p>\n\n<p>So I would recommend full-text index <strong>only</strong> in cases where you have to do a "free string search" or use some of the special features of it...</p>\n'}]
['https://Stackoverflow.com/questions/57918', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/781/']