Atitit. Software button and dashboards (13)-Full-text indexing Operations--DB database Subsystem mssql2008

Atitit. Software button and dashboards (13)-Full-text indexing Operations--DB database Subsystem mssql2008

Full-text index operations

4. Full-text index and like statement comparison 1

5. Inverted Indexes Inverted index 1

2.SQL Server 2008 Full-Text Search 2

3.Lucene Full-Text Search 3

In general, use Full-text indexing in SQL Server , after a roughly 4 - step process: 4

Mssql2008 Full-text indexing operation (attilax validation) 5

View full-text index using size 5

View table rows and volume size 6

References 6

4. Full-text index and like statement compare

Full-text indexing is, of course, highly efficient.

Generic full-text indexes use inverted indexes, which can support multiple keyword indexes, and like only when the prefix matches the ability to use the index, otherwise it is full table scan, the efficiency of course very low

However, the full-text index has a population problem and needs to be populated incrementally after adding content, otherwise the new content will not be retrieved.

In SQL Server 2008, you can use change-tracking-based padding, which is fast enough to feel like a real-time incremental population.

Author:: Old Wow's paw attilax Ayron, email:[email protected]

Reprint please indicate source: Http://blog.csdn.net/attilax

 5. Inverted Indexes Inverted index

Why is SQL Server full-text retrieval and Lucune full-text retrieval fast, because it uses inverted indexes like most search engines inverted index

2.SQL Server 2008 Full-Text Search

In order to improve the efficiency of the full-text retrieval of SQL Server, how to build a full-text search this omitted, do not mention, just look at the query method.

Example: SELECT * from table where contains (Productdesc, ' *cad* ')

Its query efficiency is very high, support Chinese word segmentation (but not good on the other said), but the disadvantage is actually in English Query, false idea fuzzy query with CAD three-letter data, it can only search out the CAD as a complete Word data, such as: it can detect ABC cad, CAD Def, or CAD, it cannot check Out of AutoCAD such a letter together with the data, that is, SQL Server full-text retrieval of the English word is a space, want to check the English words together, it can not do, you have to find another third-party full-text index, MS assumes here open an expandable incision how good, unfortunately.

Index Update fill problem: Create a good full-text search on their own initiative to completely fill, assuming that the tracking changes at the choice of their own initiative, the full-text retrieval will be based on change tracking of the population, the original table data once modified, will be quietly spread from the background, the active update of the full-text index. Detailed references http://msdn.microsoft.com/zh-cn/library/ms142575.aspx

The time before the full-text index was updated, no data was found for the new entry but not the full-text index, but like can be found. Although not real-time, but after testing, I found that the update speed is very fast, almost a change of the original table, the full-text index is updated, so I think can feel real-time, it is expected that SQL Server within the Observer pattern to achieve this function.

In addition, contains includes columns that must come from the same table, not across tables, such as where contains (A.productdesc, B.productname, ' *cad* '), which is not possible.

3.Lucene Full-Text Search

SQL Server Full-text search is not good, only to find a third-party program, the first is Lucene, but in. NET, Lucene is very smoothly.

Nlucene is a SourceForge project that ported Lucene from Java to. NET, which was converted from the Lucene 1.2 version, but stopped updating in 2002.

as the Nlucene project did not launch a new version number by 2002, but Lucene has been developing, so someone transplanted lucene 1.3. NET becomes lucene. NET, but Lucene. NET development to 2.0 version of the time turned into a commercial product, out of the open source project, heard now into the incubator has stopped development, but the officer net http://incubator.apache.org/lucene.net/ download.html See, still in the update does not seem to stop, the latest open source version number is 2.9.2, the release date is May 6, 2011, they are still preparing 2.9.4 version.

Influenced by Lucene.Net's disengagement from the open source project, some people continued to develop open source. NET Search engines. So on the basis of the original lucene.net continue to develop the project, but the name changed to Dotlucene to different from lucene.net. But now open the official website A look, got, again stopped. It seems that only the last open source version of lucene.net2.0 can be used.

Index update fill problem: It is also necessary to update the index at intervals, it is not real-time update, need to regularly update the fill talent enough, assuming that frequent updates recommended to delete the old and then rebuild the index.

Normal case, useSQL Serverthe full-text index in the. After a rough4a step:

1). Install full Text search fulltext Indexing Service;

2). Create full Text catalog Index folder for the data table;

3). Perform the population operation of full text catalog (synchronize the fulltext index with the contents of the data table);

4). Query using a full-text index.

Mssql2008the full-text index operation (Attilaxvalidation)

1. start the SQL Full-text Filter Daemon Launcher (MSSQLSERVER) service

2. Construction::: Index Table >>ritkey>> Full text index>> create Fulltxt or

D b> Storage >> Full-text folders > New full-text folders

3.Add plan >>cpu spare .....

4. 4. Use the indexing function:

SELECT * from Mybbs _table where Contains (col2, ' windows ');

Speed is also a fire.

View Full textIndexUse size

Add Status::: Processing notifications. Indicate waiting for insertion now .... Normal state:

Folder size::: 5g

View table row count and volume size

Table Properties >> Storage ... >>>35g

References

lucene.net, SQL Server 2008 Full-Text search, a little insight into like fuzzy query-bobliu-blog Park. html

Use full-text indexing in SQL Server _ Knowledge Base _ Blog Park. html

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

Atitit. Software button and dashboards (13)-Full-text indexing Operations--DB database Subsystem mssql2008