Administrators can use slow query logs to gain insight into how queries are affecting platform performance. The Slow Queries log aggregates data for similar queries. Use slow query data to evaluate the need for new indexes, changes to existing indexes, or changes to frequent queries.

Before you begin

Role required: admin

About this task

Queries are similar when they select from the same table and query the same field in the where clause, but search for different values in the field. For example, these queries are aggregated as similar queries.

SELECT * FROM sys_user WHERE name="ITIL User"
SELECT * FROM sys_user WHERE name="System Administrator"

Aggregating similar query data allows administrators to monitor the performance impact of slow queries and of queries that occur frequently. The log reports data for similar queries where the total execution time exceeds 5 seconds.

Procedure

  1. Navigate to All > System Diagnostics > Stats > Slow Queries
  2. Open a query record for more details.
    Table 1. Query record fields
    FieldDescription
    Example

    An SQL statement for an individual query, before being canonicalized to aggregate it with similar queries.
    Hash The hash value for this record.
    Example stack trace

    A stack trace for an individual query. Lines referencing script code (includes both custom and base system code) appear in the following format:

    table name.sys_id:line number

    For example, this stack trace indicates a function call from line 119 of a script include.

    sys_script_include.105f70abc0a8010300d4d79ed1b93eb0:119

    For more information, see stack traces.
    Example URL

    The URL for an individual query, depending on how the query was called.

    • User transactions: lists the transaction parameters. For example, if a user navigates to the incident list, the URL is /incident_list.do sysparm_query=active=true
    • Scheduled jobs: lists the name of the scheduled job.
    • Any other method: lists an empty field.
    First sighting

    The first occurrence of a similar query.
    Last sighting The last occurrence of a similar query.
    Total execution time Total execution time.
    Execution Count

    The number of similar query occurrences that are aggregated.
    Average execution time (ms)

    The average duration to execute one of these similar queries.
  3. To see what the database is doing to retrieve the data, click Explain Plan.
    The query plan is reported in the MySQL Explain Plan related list. Use the query plan to evaluate the need for new indexes or changes to existing indexes. For more information, see MySQL explain plan documentation.

    This feature works on other supported databases, including MongoDB and Oracle.

  4. To generate an index suggestion for a slow query, click Index Suggestion.
    For details, see Generate an index suggestion for a slow query.