When querying the product in a product introduction website, due to the introduction of the product may be very long, if you use Like to use Like to make a fuzzy query for product introduction fields, performance is definitely a problem. So how do you solve this problem? The first idea is to use full-text indexes. So what is the full-text index? How should I apply, what should I pay attention to during the application? This POST is used as a note of full-text retrieval.
1, what is it?
[Excerpt from SQL Server2000 online from books]
Full-text index provides effective support for complicated word search in string data. Full text index store information about the position of important words and these words in a particular column. The full text query uses this, quickly search for a line containing a specific word or a word.
The full-text index is included in the full-text directory. Each database can contain one or more full-text directories. A directory cannot belong to multiple databases, and each directory can contain a full-text index of one or more tables. A table can only have a full-text index, so each table with a full-text index belongs to a full-text directory.
The full-text directory and index are not stored in the database they belong. Directory and index are managed by Microsoft Search Services.
Full text index must be defined on the base table without defining on the view, system table, or temporary table.
Such a metaphor can be made based on the above description. Everyone is probably seen by the file cabinet, and the file cabinet is to register various files on the archive index card. The full-text index of the elephant in this file cabinet, through these file index cards, can quickly locate the volume you want to find s position. If these index cards are not established, if the number of volumes is not much better, once the number of files is large, it is obviously difficult to find the expected volume, which is similar to the use of Like.
The difference between full-text index and ordinary index:
Ordinary SQL Index Full-text Index
The control of the database that is defined when storing is stored in the file system, but through database management
Each table allows several normal indexes per table only allows a full-text index
When inserting, updating, or deleting as its basis, they automatically update add data to full-text index name fill, full-text index can request a scheduling or specific request, or automatically occur when adding new data
Unit grouping in the same database is set to one or more full-text directories
Use SQL Server Enterprise Manager, Wizard, or Transact-SQL statements to create and remove, create, manage, or stored, manage, or stored procedures.
2, how to use
Example: See the full-text index service using SQL Server2000
The article above is said that it is more clear. Here is just a typical SQL:
(Detailed description, you can query Contains from the book in SQL Server2000.
Returns all classification descriptions that contain strings "SEA" or "BREAD".
Use northwind
Select * from categories
WHERE CONTAINS (Description, '"Sea *" or "Bread *"')
(Detailed description, you can query freeEtext from the book in SQL Server2000)
The search product description contains all product categories related to Bread, Candy, Dry and MEAT, such as Breads, Candies, Dried, and MEATS, etc.
Use northwind
Go
Select CategoryName
From categories
WHERE FREETEXT (Description, 'Swetest Candy Bread and Dry MEAT')
Go
3. Suggestions
a, carefully consider the way to maintain a full-text index
[Excerpt from SQL Server2000 online from books]
Three ways to maintain the full text of maintenance:
Complete reconstruction
Respond all rows. Thoroughly reconstruct the full-text index. You can perform full reconstruction immediately, or by the SQL Server agent by scheduling. Incremental reconstruction based on time stamp
Rescrack the rows that have been changed from the last complete reconstruction or incremental reconstruction. Doing so need to have a TimeStAMP column on the table. Changes that do not update the timestamp (such as WriteText and UpdateText) are not detected. Incremental reconstruction can be performed immediately, or by scheduling.
Change track
Maintain a list of all changes to index data. Changes made with WRITETEXT and UPDATETEXT are not detected. You can use these changes to update the full-text index, or by scheduling, or use the background update index option to update when changing one occurs.
The method used depends on many factors, such as CPU and available memory, data changes, and speed of available disk space, and importance of current full-text index. The following suggestions can be used as a reference as the maintenance method.
When the CPU and memory is not a problem, the latest index is very high, and immediate propagation can follow the speed of the change, use the change track with the background update index option.
When the CPU and memory can be used in the schedule time, the change in the disk space for storing changes is large enough, and the change between the scheduling time is not large to make the time required for the propagation, use changes to scheduling propagation. track.
If you change or add most of the records, you should use full reconstruction. If most records are changed in the extended period of time, consider the use of change tracking with the scheduled or background update index.
If the number of documents per change is much (not the percentage of the percentage), you can use incremental reconstruction. If a large number of records are happened in the extended period of time, consider the change tracking of the indexed with scheduling or background update.
However, even if you choose a good job type, it should also give the timing of the full-text index to properly plan. Since changes in the table affect full-text index content, the frequent update data is not suitable for full-text indexes. At the same time, the time scheduled fill full-text index can be placed in the system to be idle, and the possible time to fill the fill. For example, you can set the fill time at 0:00 every night, this time should be relatively idle (this idea is a little suspicion, but in general, it should be almost the case).
In addition, you should simulate the amount of data available to make a fill experiment to estimate the length of the length of the fill index.
Ah ~~ The face involved in the full text is too wide. First finish these ~!
