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

Atitit. Software buttons 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

As a general rule, use full-text indexing in SQL Server , after a total of 4 steps: 4

Mssql2008 Full-text indexing operation (attilax validation) 5

View full-text index using size 5

View table rows and volume size 6

Reference 6

4. Full-text index and like statement comparison

Of course, full-text indexing is highly efficient.

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

However, the full-text index has a population problem and needs to be incrementally populated 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 and can almost be considered 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, only look at the query method.

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

It queries the efficiency is very high, support Chinese word segmentation (but good not to say otherwise), but the disadvantage is in English Query, if you want to fuzzy query with CAD three letters of data, it can only search out the CAD as the complete Word data, for example: it can detect ABC cad, CAD Def, or CAD, it can't find a Utocad This letter together data, that is, SQL Server full-text search of the English word word is a space, to find the English words together, it can not do, you have to find another third-party full-text index, MS if you open an expandable incision here, a pity.

update fill problem for indexes: Create a full-text search automatically to a full population, if the tracking changes in the selection of automatic, full-text retrieval will be based on change tracking of the population, the original table data once changed, will be quietly propagated from the background, automatically update the full-text index. Specific reference 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 it can be considered real-time, it is estimated that SQL Server within the Observer pattern to achieve this function.

In addition, contains contains 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.

because the Nlucene project did not launch a new version 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 Look, still in the update does not seem to stop, the latest open source version is 2.9.2, 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 the open source. Net search engine, and continue to develop the project on the basis of Lucene.Net's original, but the name changed to Dotlucene to distinguish it 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 updated in real-time, you need to update the population regularly, if you need frequent updates recommended to delete the old and then rebuild the index.

As a general rule, useSQL Serverthe full-text index in the4a step:

1). Install full Text search fulltext Indexing Service;

2). Create full text catalog for the data sheet;

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 Catalogs > New full-Text Catalogs

3.Add plan >>cpu idle ...

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. Indicates the current waiting for insertion .... Normal state:

Directory Size::: 5g

View table row count and volume size

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

Reference

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

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