⚡ SQL Query Optimizer Tool

Analyze SQL. Inspect the real query plan. Recommend an index. Measure the improvement.

A production-style SQL performance analysis and optimization tool built with Python, SQLite, and Streamlit.

The application analyzes SQL queries, detects common performance anti-patterns, runs the real SQLite EXPLAIN QUERY PLAN, generates concrete index recommendations, and validates those recommendations with measured before/after benchmarks.

---

📌 Overview

SQL performance problems can be difficult to diagnose. Developers often need to understand query plans, table scans, indexing strategies, and database behavior before they can determine why a query is slow.

This project provides a complete optimization workflow in one tool:

SQL Query
    ↓
Static Analysis
    ↓
Anti-Pattern Detection
    ↓
Real EXPLAIN QUERY PLAN
    ↓
Index Recommendation
    ↓
Before/After Benchmark
    ↓
Performance Report

The tool is available through both:

• Command-line interface (CLI)
• Streamlit web dashboard

---

✨ Features

🔍 SQL Anti-Pattern Detection

Detects common SQL performance issues including:

• SELECT *
• Leading-wildcard searches such as LIKE '%keyword'
• Functions applied to columns
• Missing WHERE clauses
• Comma-style / implicit joins

Each detected issue includes an actionable recommendation.

---

📊 Real Query Plan Analysis

The application executes the actual SQLite:

EXPLAIN QUERY PLAN

against the sample database.

It does not simulate or guess the execution plan.

The analyzer can identify:

• Full table scans
• Index searches
• Table searches
• Query-plan operations

---

🧠 Size-Aware Scan Detection

Not every full-table scan is a performance problem.

A small lookup table may be scanned efficiently, while scanning a large table can become expensive.

The tool therefore uses a configurable table-size threshold before reporting a full scan as an optimization issue.

---

🛠️ Concrete Index Recommendations

The optimizer extracts relevant columns from the query and generates a ready-to-use SQL statement.

Example:

CREATE INDEX idx_tracks_genre_id
ON tracks (genre_id);

This makes the recommendation directly actionable instead of simply telling the developer to "consider an index."

---

⚡ Real Before/After Benchmarking

The suggested index can be validated with an actual benchmark.

The process is:

Query without index
        ↓
Baseline timing
        ↓
Temporary database copy
        ↓
Apply suggested index
        ↓
Optimized timing
        ↓
Calculate speedup

The original database is never permanently modified during benchmarking.

---

🖥️ Streamlit SaaS Dashboard

The web interface provides:

• Query analysis
• Sample query library
• KPI cards
• Interactive results
• Execution-plan information
• Index recommendations
• Benchmark results
• Search/filter controls
• Loading states
• Empty states
• Success/error messages
• CSV/TXT report downloads

---

🧰 Technology Stack

Technology	Purpose
Python 3.12	Core application
SQLite	Database engine
sqlparse	SQL statement parsing
pandas	Data handling and dashboard tables
Streamlit	Web dashboard
pytest	Automated testing
CSV	Report/export support

---

🏗️ Architecture

                         SQL Query
                            │
             ┌──────────────┴──────────────┐
             │                             │
             ▼                             ▼
    ┌─────────────────┐          ┌─────────────────┐
    │ query_analyzer  │          │ plan_inspector  │
    │                 │          │                 │
    │ Static SQL      │          │ Real SQLite     │
    │ anti-patterns   │          │ EXPLAIN PLAN    │
    └────────┬────────┘          └────────┬────────┘
             │                            │
             └──────────────┬─────────────┘
                            ▼
                  ┌──────────────────┐
                  │  index_advisor   │
                  │                  │
                  │ WHERE-column     │
                  │ extraction       │
                  └────────┬─────────┘
                           │
                           ▼
                    Index Suggestion
                           │
                           ▼
                  ┌──────────────────┐
                  │    benchmark     │
                  │                  │
                  │ Before / After   │
                  │ timing            │
                  └────────┬─────────┘
                           │
                           ▼
                ┌─────────────────────┐
                │ optimizer_service   │
                │                     │
                │ Pipeline            │
                │ Orchestration       │
                └──────────┬──────────┘
                           │
                  ┌────────┴────────┐
                  ▼                 ▼
             ┌─────────┐       ┌─────────┐
             │ main.py │       │  app.py │
             │   CLI   │       │Streamlit│
             └─────────┘       └─────────┘

Core Modules

Module	Responsibility
query_analyzer.py	Static SQL anti-pattern detection
plan_inspector.py	Real SQLite query-plan analysis
index_advisor.py	Index recommendation generation
benchmark.py	Before/after performance measurement
optimizer_service.py	Orchestrates the complete optimization pipeline
sample_database.py	Creates the local sample database
exceptions.py	Custom application exceptions
config.py	Application configuration and thresholds

---

📁 Project Structure

sql-query-optimizer/
│
├── src/
│   ├── config.py
│   ├── exceptions.py
│   ├── sample_database.py
│   ├── query_analyzer.py
│   ├── plan_inspector.py
│   ├── index_advisor.py
│   ├── benchmark.py
│   └── optimizer_service.py
│
├── tests/
│   ├── test_sample_database.py
│   ├── test_query_analyzer.py
│   ├── test_plan_inspector.py
│   ├── test_index_advisor.py
│   ├── test_benchmark.py
│   └── test_optimizer_service.py
│
├── data/
│   └── sample_store.db
│
├── main.py
├── app.py
├── requirements.txt
├── GUIDE.txt
├── README.md
└── LICENSE

---

🚀 Installation

Prerequisites

Make sure the following are installed:

• Python 3.11+
• Git
• pip
• VS Code or another code editor

Check Python:

python --version

Check Git:

git --version

---

1. Clone the Repository

git clone <YOUR-REPOSITORY-URL>

Move into the project directory:

cd sql-query-optimizer

---

2. Create a Virtual Environment

Windows

python -m venv venv

Activate it:

venv\Scripts\activate

macOS / Linux

python3 -m venv venv

Activate it:

source venv/bin/activate

---

3. Install Dependencies

pip install -r requirements.txt

---

▶️ How to Run

Follow these steps in order.

Step 1 — Build the Sample Database

Run:

python main.py --build-db

This creates the local SQLite database used by the optimizer.

The database is generated under:

data/sample_store.db

To rebuild the database from scratch:

python main.py --build-db --force-rebuild

---

Step 2 — Analyze a SQL Query

Run:

python main.py --analyze "SELECT * FROM tracks WHERE genre_id = 3"

The tool will analyze the query for:

• SQL anti-patterns
• Full-table scans
• Query-plan behavior
• Index opportunities

---

Step 3 — Analyze a SQL File

Create a file such as:

my_query.sql

Add:

SELECT *
FROM tracks
WHERE genre_id = 3;

Then run:

python main.py --analyze-file my_query.sql

---

Step 4 — Benchmark the Query

Run:

python main.py --benchmark "SELECT * FROM tracks WHERE genre_id = 3"

The benchmark compares query performance before and after applying the suggested index.

Example:

Before: 0.0042 seconds
After:  0.0029 seconds
Speedup: 1.45x

Actual values depend on the machine and execution environment.

---

🌐 Run the Streamlit Dashboard

After building the database, launch:

streamlit run app.py

Streamlit will display a local URL similar to:

Local URL: http://localhost:8501

Open that address in your browser.

---

🖥️ Dashboard Workflow

1. Analyze a Query

Open:

Analyze a Query

from the sidebar.

2. Select a Sample Query

Choose one of the included sample queries.

3. Enter Your Own Query

You can also enter custom SQL.

Example:

SELECT *
FROM tracks
WHERE genre_id = 3;

4. Run Analysis

Click:

Run Analysis

The dashboard displays:

Anti-Patterns
      ↓
Execution Plan
      ↓
Index Recommendations

5. Benchmark the Recommendation

Click:

Benchmark Top Suggestion

to measure the real before/after performance.

6. Download the Report

Analysis results can be exported as:

• CSV
• TXT

---

🗄️ Dataset

The project uses a Chinook-style relational database schema containing:

• Artists
• Albums
• Tracks
• Genres
• Customers
• Invoices
• Invoice Items

The schema is designed to provide realistic relationships between tables for SQL query-plan and indexing analysis.

The local database generator recreates this schema and generates reproducible data using a fixed seed.

The generated dataset contains approximately:

2,500 tracks
4,500 invoice items
350 customers
15 genres

The original Chinook database could not be downloaded in the restricted development environment, so the project includes a local schema-compatible generator.

The application can also work with a compatible SQLite database when configured appropriately.

---

🧪 Testing

Run the complete test suite:

python -m pytest tests/ -v

Current test suite:

49 tests
49 passing

The tests cover:

• Anti-pattern detection
• Positive and negative cases
• Database generation
• Foreign-key integrity
• Query-plan inspection
• Full-scan detection
• Small-table false-positive prevention
• Index recommendations
• Multi-table query handling
• Benchmark isolation
• Service orchestration

---

🐛 Real Bug Found and Fixed

During testing, a real bug was discovered in multi-table index recommendation.

For example:

SELECT *
FROM tracks, albums
WHERE albums.title = 'X';

The incorrect implementation could associate:

albums.title

with the wrong table.

This could result in an invalid recommendation such as:

CREATE INDEX idx_tracks_title
ON tracks (title);

even though tracks does not contain a title column.

Fix

The index-advisor logic was updated to correctly handle table-qualified columns:

table.column

For multi-table queries, qualified columns are attributed only to their referenced table.

Regression tests were added to ensure this behavior remains correct.

---

🧠 Engineering Decisions

Static Analysis and Runtime Analysis Are Separate

The project intentionally separates:

What does the SQL look like?

from:

What is the database actually doing?

Static analysis identifies suspicious query patterns, while EXPLAIN QUERY PLAN reveals the database's actual execution strategy.

---

Full Scans Are Size-Aware

A full scan of a tiny table may be perfectly reasonable.

Therefore, the tool uses a configurable threshold to avoid producing unnecessary warnings.

---

Benchmarking Does Not Modify the Original Database

Benchmarking is performed on a temporary database copy.

Original Database
       ↓
Temporary Copy
       ↓
Apply Index
       ↓
Run Benchmark
       ↓
Compare Results
       ↓
Temporary Copy Removed

This allows benchmarks to be safely repeated.

---

Centralized Service Layer

optimizer_service.py orchestrates the complete optimization workflow.

Both:

main.py
app.py

use the same service layer instead of duplicating business logic.

---

⚠️ Troubleshooting

UnsupportedStatementError

The current version supports SELECT statements.

Unsupported examples:

INSERT ...
UPDATE ...
DELETE ...

Use a SELECT query instead.

---

QueryExecutionError

This usually means the query references a table or column that does not exist in the sample database.

Check the available tables:

artists
albums
tracks
genres
customers
invoices
invoice_items

---

Streamlit Dashboard Is Blank

Build the database first:

python main.py --build-db

Then launch:

streamlit run app.py

---

ModuleNotFoundError

Make sure your virtual environment is activated.

Windows:

venv\Scripts\activate

Then reinstall:

pip install -r requirements.txt

---

Port Already in Use

Run Streamlit on another port:

streamlit run app.py --server.port 8502

Then open:

http://localhost:8502

---

☁️ Deployment

Streamlit Community Cloud

Streamlit Community Cloud is the recommended deployment option for this project.

Steps

1. Push the repository to GitHub.
2. Open Streamlit Community Cloud.
3. Connect your GitHub account.
4. Select the repository.
5. Select branch:

main

6. Set the main application file:

app.py

7. Deploy.

The dependencies will be installed from:

requirements.txt

Important

Because the application requires the sample SQLite database, the deployment configuration should ensure that the database is available/generated before analysis begins.

---

Render

The project can also be deployed as a Streamlit web service.

Build Command

pip install -r requirements.txt

Start Command

streamlit run app.py --server.port $PORT --server.address 0.0.0.0

---

🔮 Future Roadmap

Version 2.0

🤖 AI-Assisted Query Rewriting

Use an LLM to generate optimized SQL rewrites for complex queries.

🗄️ Custom SQLite Databases

Allow users to upload their own SQLite databases for analysis.

🐘 Multi-Database Support

Add support for:

• PostgreSQL
• MySQL

with database-specific execution-plan analysis.

📊 Large-Scale Benchmarking

Support datasets containing:

100,000+ rows

to demonstrate indexing impact at larger scales.

📈 Query History

Store historical query analyses and performance results.

🧩 Composite Index Recommendations

Generate multi-column indexes when queries filter on multiple columns.

---

👨‍💻 Author

Sumair Ahmed Dero

BS Artificial Intelligence Student University of Sindh, Jamshoro

I am an AI-focused developer interested in building practical, production-oriented software systems and intelligent developer tools.

Areas of Interest

• Artificial Intelligence
• Machine Learning
• Natural Language Processing
• AI Research
• Software Engineering
• Intelligent Developer Tools

Technical Focus

Python
Machine Learning
Artificial Intelligence
NLP
SQL & Databases
Software Engineering

---

🤝 Contributing

Contributions are welcome.

1. Fork the repository

git clone <YOUR-REPOSITORY-URL>

2. Create a feature branch

git checkout -b feature/your-feature

3. Follow the project standards

• PEP 8
• Type hints
• Clear naming
• Google-style docstrings
• Logging
• Error handling
• Automated tests

4. Run the tests

python -m pytest tests/ -v

5. Submit a Pull Request

Include a clear description of:

• What changed
• Why it changed
• How it was tested

---

📄 License

This project is licensed under the MIT License.

See the LICENSE file for details.

---

⭐ Support

If you find this project useful:

• ⭐ Star the repository
• 🐛 Report issues
• 💡 Suggest improvements
• 🤝 Contribute to the project

---

📌 Project Summary

SQL Query Optimizer Tool demonstrates a practical database-performance workflow:

Detect
  ↓
Inspect
  ↓
Recommend
  ↓
Benchmark
  ↓
Measure

Don't guess that an optimization works. Measure it.