Search Administration - Best Practices

Search Administration - Best Practices

  1. Apply active in the filter condition to enable queries to process on smaller result sets
  2. Use is and start with ANDing queries
  3. Use Date Fields such as syscreatedon or sysupdatedon on filter and searchs
  4. Combine both or add a combination of idnexes on large tables with the active and dateTime fields

DON'T search administration DONTS

  1. Index every field
  2. index data type of longtext, such short description, comments, and work notes fields
    1. Index on these fields may not be as usefull because only the first few characters are indexed
    2. on the contrary if you search string is small for example searching for incidents with memory low, the index could be benefecial
  3. Use contains and or in your filter or searchs, when possible, as the mysql optimizer may do a full table scan

MEDIUMTEXT

  • Usually, on MEDIUMTEXT data type fields, indexes are only beneficial if the condition used to filter or search is starts with.

CONTAINS

  • If using contains instead would usually not improve performance.
  • For example, creating an index for data type fields would not make a major impact if the condition used to search for incidents with memory low is contains instead of starts with.

When does ServiceNow decide to use indexes?

In normal cases, a customer creates an incident and reports that either a particular part in ServiceNow application is slow or general slowness. This is the moment where the administrator starts to look into slow SQL.

Options to see the slow SQL

  • Mainly, there are two options to see the Slow SQL, using the Slow Queries module or the localhost_log of each application node.

Localhost_log

  • In the localhost_log, each transaction is logged and every SQL part of a transaction that takes more than 100 milliseconds is logged as well. Once the slow SQL is found, it is analyzed which leads to the administrator decide if an index needs to be created.

How do you know a new index needs to be created?

  • The easiest way is to run an Explain Plan on the found slow SQL.

Explain Plan

  • The Explain Plan displays how many rows the database engine will scan on the tables involved in the query.
  • Depending on the fields involved in the WHERE clause, the administrator may decide that an index on a field, used in the query, may be faster than what the database shows.

How do you apply an index once it is created?

  • Once an index is created, the database uses it automatically.

results matching ""

    No results matching ""