Skip to main content
SQLOZ
Functies Documentatie Prijzen Roadmap Blog Ons verhaal Beschikbaar in de Mac App Store
Chapter 19 OzSuite™ AI & Advanced Tools

Chapter 19: OzExplain - Execution Tree & Index Advisor

Info: This chapter explains converting supported EXPLAIN output into a visual execution tree, estimated-vs-actual row divergence warnings, and index recommendations. Availability depends on the server’s capability probe.

In this chapter:

19.1 Visual Execution Tree

When you select a read query and press Cmd + E, OzExplain visualizes the available EXPLAIN output as colored nodes. SQLite and servers that do not expose the requested explain form show the corresponding unavailable/error state.

  • Red nodes (bottlenecks): Inefficient steps such as Full Table Scan (ALL), disk-based filesort or using temporary.
  • Green nodes (fast steps): Steps using a primary key or a B-Tree index.

This view lets you determine at a glance which point of the query creates the bottleneck.

19.2 Estimated-vs-Actual Row Comparison

When the connected server supports an analyze form, OzExplain compares the optimizer’s estimated scanned rows with actual rows. MySQL uses EXPLAIN ANALYZE; MariaDB uses its supported ANALYZE form. SQLite has no EXPLAIN ANALYZE path.

When the divergence exceeds 3x, the following warning appears on the node:

ANALYZE TABLE orders; -- Actual rows (450,000) exceed estimate (1,000) by 450x

Tip: This warning indicates that the database statistics are stale and that ANALYZE TABLE should be run.

19.3 Index Recommendations

When a Full Table Scan (ALL) is detected, OzExplain can display an index recommendation for the scanned table:

CREATE INDEX idx_orders_opt ON orders (<filter_columns>);

The recommendation is a suggested statement shape; SQLOZ does not infer the filter columns in this view. Review the columns against the real workload and apply it through the normal safety gate; SQLOZ does not silently create the index.