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.
  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.