SQLite Cloud

Query Analyzer

The Query Analyzer panel is a powerful tool that collects and categorizes all the queries executed on your cluster based on their execution time. It allows for intelligent and proactive analysis, and provides recommendations on which indexes to use to optimize frequently used queries.

Getting Started

By default, the Analyzer is disabled to avoid unnecessary overhead. You can enable it directly from the top-left dropdown menu in the Analyzer panel.

Simply click on the dropdown (initially labeled Disabled) and select a monitoring threshold. You can choose a preset value (e.g., Threshold 10ms, Threshold 100ms) or define a Custom Threshold. Only queries taking longer than the selected time (in milliseconds) will be recorded and analyzed.

Query Analyzer is a debugging tool.
It’s recommended to keep it active only for the time strictly necessary to identify and optimize queries.

Testing the Analyzer

To test the Analyzer, we can navigate to the Studio -> chinook.sqlite -> SQL Console section and perform a query that filters the non-indexed Composer column of the Track table using the following statement:

SELECT * FROM Tracks WHERE Composer = 'AC/DC';

Once the query is executed, return to the Analyzer panel. You will see the query listed in the table along with execution statistics, such as Count, Avg. Time (ms), and Max Time (ms).

Analyzing Performance and Applying Suggestions

Click on the query row to open the Query Details side panel. This panel provides in-depth information organized into three tabs:

  1. Query: Displays the full SQL statement (with an option to copy it).
  2. Current Execution Plan: Shows how the database engine currently processes the query (e.g., SCAN TABLE indicates a full table scan, which is often inefficient).
  3. Index Suggestions: This is the most critical section for optimization. It displays Candidate Indexes and a Suggested Index.

To optimize your database performance, navigate to the Index Suggestions tab.

If an optimization is available, you will see a proposed CREATE INDEX statement.

Simply click the Apply All Suggestions button. The Analyzer will automatically create and distribute the optimal index in the chinook.sqlite database, speeding up future queries filtered by the Composer column.