Principles of search engines in dnn (Database Analysis)

The Search Method Used in dotnetnuke (dnn for short) is not the one we usually imagine directly searching for in the table to be queried. Because dnn has a variety of modules, the form of each data table is different from the field to be queried. It is almost impossible to directly query the content of each table. A searchitem table is introduced in dnn to add various types of content to be queried to the table in the form of query entries. At the same time, a searchword table is also introduced, which breaks down the content to be queried into keywords, which can accelerate the retrieval speed and facilitate multi-Keyword retrieval. Another tips in dnn search: You can use "key1 key" 2" You can use the "key1 + key2-" format to explicitly specify whether a keyword must be included or excluded.

The entire and search-related table relationship diagram is as follows:

 

From the graph, you only need to find the specified keyword in the searchword table and use the searchitemword table to know the content to be queried. By using the searchitem table, you can know exactly which module is used.

Detailed descriptions of fields in each table:

Searchcommonwords:★Common Words/phrases information table (stores common words in the specified cultural area, and input these words during query will not return results)

Field name	Type	Description	Remarks
Commonwordid	Int	Common Word ID	Primary Key
Commonword	Nvarchar (255)	Common Words	For example, where and about are common words in English.
Locale	Nvarchar (10)	Cultural Region

 

Searchindexer:★Index the provider Assembly (the data in this table seems useless and has been set in Web. config)

Field name	Type	Description	Remarks
Searchindexerid	Int	ID	Primary Key
Searchindexerassemblyqualifiedname	Char (200)	Assembly

 

Searchitem:★Module Content Search entries (save all module content that may be searched in the form of search entries by means of regular scheduling, which can speed up searching)

Field name	Type	Description	Remarks
Searchitemid	Int	Search entry ID	Primary Key
Title	Nvarchar (200)	Title
Description	Nvarchar (2000)	Description
Author	Int	Author ID
Pubdate	Datetime	Release Date
Moduleid	Int	Module ID	Associate with module information table (modules)
Searchkey	Nvarchar (100)	Search keywords
Guid	Varchar (200)		Indicates a record in the module.
Hitcount	Int	Clicks
Imagefileid	Int

 

Searchitemword:★Search keyword index information table (relationship between search entries and search keywords)

Field name	Type	Description	Remarks
Searchitemwordid	Int	Search keyword index ID	Primary Key
Searchitemid	Int	Search entry ID	Searchitem)
Searchwordsid	Int	Search Keyword ID	Join search keyword info table (searchwordsid)
Occurrences	Int	Number of occurrences	Number of times a keyword appears in a specified search

 

Searchitemwordposition:★Position where the search keyword appears in the searched content

Field name	Type	Description	Remarks
Searchitemwordpositionid	Int	ID	Primary Key
Searchitemwordid	Int	Search keyword index ID	Searchitemword)
Contentposition	Int	Location

 

Searchword:★Search Keyword information table (storing available keywords)

Field name	Type	Description	Remarks
Searchwordsid	Int	Search Keyword ID	Primary Key
Word	Nvarchar (100)	Search keywords
Iscommon	Bit	Whether it is a common word
Hitcount	Int	Clicks

An Improved stored procedure:

View the Stored Procedure getsearchresults for obtaining the query result. We will find that the search keyword must be completely matched to find the desired content. The problem lies in the sentence "Sw. Word = @ word". That is to say, if a module contains the content "dotnetnuke", it will not be identified by inputting "nuke. Change this sentence to "Sw. Word like '%' + @ word + '%'" (this is a way to change the result with efficiency ). See the specific modification method: http://www.cnblogs.com/esshs/archive/2005/08/12/213154.html

 

In terms of data tables and functions, data fields in some tables are not used. It is estimated that the reserved dnn fields can be expanded in the future. After reading this article, I wonder if you have a rough understanding of the dnn query function. In the future, we will continue to explain how the dnn search engine works by filling in the searchitem table through the query interface implemented by each functional module and clicking "query. If any of the above explanations are incorrect, please point out that we should avoid misleading others.