另外，我能做些什么向列
  的数据库，以便它们可以
  搜索速度更快？我读的东西
  关于索引他们，就是刚刚
  全文索引真/假场
  我在SQL Management Studio中看到了什么？

块引用>

是，使全文索引通常会大大有助于提高性能这个场景很长的路要走。但不幸的是它不会自动与LIKE操作人员的工作（这就是你的LINQ查询生成）。所以，你将不得不使用像FREETEXT，FREETEXTTABLE，含有或CONTAINSTABLE。

内置的全文搜索功能之一

只是解释，原来的code将比全文搜索慢得多，因为它通常会导致表扫描。例如，如果你要搜索一个名为标题与LIKE'％ABC％'一个varchar字段，有没有选择，但对于SQL扫描每一个记录看它是否包含这些字符。

不过，内置的全文检索，实际上指数可以指定每列的文本在全文索引包括。而且它是一个大大加快你的查询索引。

不仅如此，但全文搜索提供了一些很酷的功能，LIKE操作符不能给你。它不像谷歌那样复杂，但它有搜索根词的替代版本的能力。但我最喜欢的功能之一是排名功能，它可以返回一个额外的值，以指示相关性，您就可以使用您的结果进行排序。要使用窥视 FREETEXTTABLE 或的 CONTAINSTABLE 功能。

一些更多的资源：

• 全文搜索（SQL Server）的


• 在SQL Server的临全文搜索2008

I'm attempting to implement complete search functionality in my ASP.NET MVC (C#, Linq-to-Sql) website.

The site consists of about 3-4 tables that have about 1-2 columns that I want to search.

This is what I have so far:

    public List<SearchResult> Search(string Keywords)
    {
        string[] split = Keywords.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
        List<SearchResult> ret = new List<SearchResult>();
        foreach (string s in split)
        {
            IEnumerable<BlogPost> results = db.BlogPosts.Where(x => x.Text.Contains(s) || x.Title.Contains(s));

            foreach (BlogPost p in results)
            {
                if (ret.Exists(x => x.PostID == p.PostID))
                    continue;

                ret.Add(new SearchResult
                {
                    PostTitle= p.Title,
                    BlogPostID = p.BlogPostID,
                    Text=p.Text
                });

            }
        }
        return ret;
    }

As you can see, I have a foreach for the keywords and an inner foreach that runs over a table (I would repeat it for each table).

This seems inefficent and I wanted to know if theres a better way to create a search method for a database.

Also, what can I do to the columns in the database so that they can be searched faster? I read something about indexing them, is that just the "Full-text indexing" True/False field I see in SQL Management Studio?

Also, what can I do to the columns in the database so that they can be searched faster? I read something about indexing them, is that just the "Full-text indexing" True/False field I see in SQL Management Studio?

Yes, enabling full-text indexing will normally go a long way towards improving performance for this scenario. But unfortunately it doesn't work automatically with the LIKE operator (and that's what your LINQ query is generating). So you'll have to use one of the built-in full-text searching functions like FREETEXT, FREETEXTTABLE, CONTAINS, or CONTAINSTABLE.

Just to explain, your original code will be substantially slower than full-text searching as it will typically result in a table scan. For example, if you're searching a varchar field named title with LIKE '%ABC%' then there's no choice but for SQL to scan every single record to see if it contains those characters.

However, the built-in full-text searching will actually index the text of every column you specify to include in the full-text index. And it's that index that drastically speeds up your queries.

Not only that, but full-text searching provides some cool features that the LIKE operator can't give you. It's not as sophisticated as Google, but it has the ability to search for alternate versions of a root word. But one of my favorite features is the ranking functionality where it can return an extra value to indicate relevance which you can then use to sort your results. To use that look into the FREETEXTTABLE or CONTAINSTABLE functions.

Some more resources:

• Full-Text Search (SQL Server)
• Pro Full-Text Search in SQL Server 2008