Introducing Soar: Features, Usage Scenarios, and Practical Examples for SQL Optimization

Soar is an open‑source "SQL optimization神器" that has quickly attracted over 2,700 GitHub stars, with many community contributors offering suggestions and improvements.

Basic usage : Users can pipe SQL directly to Soar, e.g., echo "select title from sakila.film" | ./soar -log-output=soar.log , or read SQL from a file using the -query flag.

Configuration file : If no explicit config is provided, Soar searches /etc/soar.yaml , ./etc/soar.yaml , and ./soar.yaml in decreasing priority. An example soar.yaml is shown with online and test DSN settings.

Heuristic rules : The command soar -list-heuristic-rules lists all built‑in heuristic suggestions. Users can ignore specific rules with -ignore-rules "ALI.001,IDX.*" or disable them via configuration.

Report formats : Soar supports markdown (default), JSON, HTML, and other formats selectable with -report-type . Available types can be listed via soar -list-report-types .

Syntax checking : The -only-syntax-check flag validates SQL syntax without performing a full review, returning exit codes 0 for success and 1 for errors.

SQL rewrite : Soar can rewrite DML statements to equivalent SELECTs (e.g., echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter -report-type rewrite ) and merge multiple ALTER statements into a single statement using the mergealter rule.

EXPLAIN analysis : Users can feed MySQL EXPLAIN output (plain table or JSON) to Soar with soar -report-type explain-digest to obtain a readable analysis and recommendations.

Markdown to HTML : Soar can convert markdown reports to HTML via cat test.md | soar -report-type md2html > test.html , with additional options for CSS, JavaScript, and markdown extensions.

Cleaning test artifacts : The -cleanup-test-database flag removes temporary tables prefixed with optimizer_ that may remain after a test run.