Indexes are meant to improve saved or advanced ad-hoc search speed for specific fields. The system automatically performs a full text index of all data in the system on all tables, including in attached files. The Indexes tab in the Table wizard allows administrators to create additional indexes for specific fields that are used very frequently in searching to speed up those searches. From the Indexes tab, you can also change the fields that appear in the drop-down of the main search block.

Creating Indexes

You should only turn on indexing for specific tables if you are comfortable working with databases, have a good feel for the makeup of the data, and know which searches are most frequently used. If a certain field is often used for searching, and the number of matching records is much lower than the number of records in the knowledgebase, you can improve search speed by adding a database index for that field.

For example, adding an index to the Assigned To field will increase performance if there are 100 records assigned to a particular team or individual in a database of 100,000 records. However, if there are 50,000 records assigned to that team or individual, adding indexing will not help at all and may actually hurt performance.

To set up a field index:

1. Click the Setup gear in the top-right corner and go to Tables.
2. Select the table where you want to add an index and click Edit.
3. Click the Indexes tab.
4. Click New.
5. Select the field you want to index and click Finish.

When you save the index setting, the system takes the time to index the selected field immediately. 

Typically, you would only index a single field at a time, but it is possible to create a compound index by choosing multiple fields. For example, if you have a frequently-used saved search that uses both the Status and Assigned to fields, and the number of matching records is much lower than the number of records in the knowledgebase, indexing can improve the performance of that saved search.

Considerations for Creating Indexes

If you are unsure whether adding an index is right for your situation, contact  Agiloft consulting services to discuss the options or to request assistance.

Because database indexing that is implemented poorly can adversely affect knowledgebase performance, it is important to be careful and consider the following:

• Database locking makes the table unavailable while you are adding an index. For this reason, consider adding indexing outside of working hours.
• Inappropriate or excessive indexes will actually hurt performance. In general, tables should not have more than 7 or 8 indexes.

Synchronizing Indexes

Indexes may sometimes become out of sync with the system database, which can prevent the index from working properly. This can occur if the system is restarted while an index is being created or deleted. You can use the Check Indexes button and Synchronize Indexes button on the Indexes tab to identify and address synchronization issues:

• The Check Indexes button compares the indexes found in the database with the indexes recognized by  Agiloft and reports discrepancies. The button opens the report in a new window that lists the index name, the indexed fields recognized by  Agiloft, the indexed fields found in the database, and the index status.
• The Synchronize Indexes button syncs the table's indexes with the database. The amount of time to complete this process depends on the number of records in the table. For example, a table containing one million records takes about five minutes to sync. You only need to sync with the database if the Check Indexes button reports a problem, or if you otherwise suspect that the database is out of sync, such as a log file message that reports a synchronization issue.

Turning Off Indexing

The indexing process is resource intensive and should be turned off for tables where it is unnecessary, such as tables that do not require instantaneous searches of their content. The Communications table is a good example of where indexing should be turned off because searching on this table is not common. Turning off indexing means that you cannot choose to make searches on certain fields faster, but it will improve the overall search performance of the entire table.

To turn off field indexing:

1. Go to Setup > Tables.
2. Select the table where you want to turn off indexing and click Edit.
3. Click the Indexes tab.
4. Clear the "Create FTS index for this table" checkbox.
   
   
5. Click Finish.