Skip to main content
Version: Next

Full-Text Search and Indexed Columns

Full-text search makes keyword searches fast by using an index that the database maintains, instead of scanning text columns for every query. Cortex builds and maintains those indexes inside the archive database — it never writes to your source system.

Where a full-text index is available, Cortex uses it. Where one is not available — the index was never built, the archive account cannot read it, or the columns a search needs are not covered by it — Cortex falls back to a slower "contains" style search so results keep coming back. Users see this as Fallback Mode; see Searching for what they encounter on the Search page.

Where the Index Lives​

For native ServiceNow mirrors, the index lives on the physical table that owns the full-text artifacts, not necessarily the logical table you are looking at:

  • Tables that inherit from task (incident, change_request, problem, sc_task, and any custom task-based ticket tables) all share the physical task table, so they also share one full-text index.
  • Cortex builds and queries that shared index on the parent task table, and uses the same column list on both sides so the index and the search always agree.

Other source systems use the full-text support of their own database — for example, Cherwell archives rely on the full-text catalogs configured in the Cherwell database. The column configuration described below applies to ServiceNow data sources.

note

The Cortex database itself also requires SQL Server Full-Text Search to be enabled. See System Requirements.

Configuring Indexed Columns (ServiceNow)​

Open Admin → Data Sources, edit your ServiceNow data source, and go to the Indexed Columns tab.

The tab lists every mirrored table that can be indexed. For each table you get:

  • A status indicator — green when the index is built, yellow when it is partially built, and grey when it has not been built.
  • A short summary of which columns that table's index covers.
  • An Edit button that opens the column picker for that table.

There are two ways to view the list:

  • Logical tables — browsable ServiceNow tables by their logical name, the view to use for day-to-day configuration.
  • Physical tables — the exact table and view names the search queries, which is useful when a logical table maps onto a shared physical table.

Choosing the Columns​

The column picker for a table offers the text-type columns that physically exist on the table that owns the index, plus the standard core columns. For each table you can either:

  • Leave it unconfigured — the standard core columns (number, short_description, description, sys_id) are indexed, which is the behavior Cortex has always used.
  • Pick individual columns — labels come from your ServiceNow metadata, and the physical column is what gets stored and searched.
  • Check Index every text column on this table — every text-type column physically present on the index target is indexed. This makes every text field searchable at the cost of a larger index.

Save columns for this table stores the choice with the data source; Back to standard columns returns that table to the default core set. Both index building and searching read this configuration, so they stay in step.

Shared task index

Because task subclasses share one index on the parent task table, the columns that are actually indexed are the union of what every configured sibling table asks for, plus the standard core columns. A sibling table you have not configured keeps searching the same shared index with the core columns. The column picker tells you when a table's index is shared with other task-based ticket tables.

Building the Indexes​

The same tab has a Build Full Text Indexes action:

  1. Tick the tables you want to build (use the filter box to find them quickly).
  2. Choose Build Full Text Indexes (n) to build them. Progress, the current step, and remaining actions are shown per table.
  3. Use Check Current FTS Status at any time to see what the database currently has, without changing anything.
  4. Use Show SQL to create manually to generate the index DDL without executing it, so it can be reviewed or handed to a DBA.

Building or rebuilding an index writes to the archive database, so the connection account needs more than the read-only access Cortex requires for normal operation. If your archive account is read-only, use Show SQL to create manually and have your DBA run the statements.

Changing the columns later​

Changing a table's column list does not update an index that is already built — the index has to be rebuilt for the new column set:

  • On SQL Server the column list of an existing full-text index cannot be changed in place, so Cortex validates the replacement column set and then drops and recreates the index.
  • On MariaDB / MySQL the column list of a FULLTEXT index must match the MATCH(...) list exactly, so Cortex keeps the generated index and the search predicate using the same resolved list.
  • On PostgreSQL the index vectors are rebuilt so columns removed from the configuration do not leave stale values behind.

Until the index is rebuilt, searches that need an uncovered column stay correct — Cortex verifies that the columns a search asks for are actually indexed and uses the slower fallback search when they are not, instead of returning an error.

See Also​