SHOW PROFILES - MySQL

Overview

The SHOW PROFILES command in MySQL provides insights into query performance by displaying profiling information for executed queries. It helps identify slow queries and bottlenecks, enabling database optimization.

Syntax

SHOW PROFILES
[LIMIT number_of_rows]
[OFFSET offset_number]
[WHERE condition]
[ORDER BY sort_order]

Parameters:

  • LIMIT number_of_rows: Limits the number of results displayed.
  • OFFSET offset_number: Skips the specified number of results before displaying.
  • WHERE condition: Filters results based on specified conditions.
  • ORDER BY sort_order: Sorts the results by a specific column (e.g., Query_ID, Duration).

Options/Flags

None.

Examples

Displaying profiling information for the last 10 queries:

SHOW PROFILES LIMIT 10;

Filtering profiles by duration:

SHOW PROFILES WHERE Duration > 100;

Sorting profiles by query duration in descending order:

SHOW PROFILES ORDER BY Duration DESC;

Common Issues

  • Insufficient privileges: Ensure the user has the SHOW PROFILE privilege.
  • Query not executed: If the query has not been executed, there will be no profiling data to display.

Integration

SHOW PROFILES can be used with the EXPLAIN command to gain deeper insights into query performance. For example:

EXPLAIN SELECT * FROM table;
SHOW PROFILES;
  • EXPLAIN: Provides a visual representation of query execution plans.
  • SET PROFILE: Enables or disables query profiling.
  • PERFORMANCE_SCHEMA: Database containing detailed performance information.