Generate SQL Indexes from JSON Query Patterns

Index Generation

Indexes speed up query performance by allowing the database to find rows without scanning the entire table. The converter generates CREATE INDEX statements for columns that are commonly filtered, sorted, or joined.

Example JSON

{
  "id": 1,
  "user_id": 42,
  "status": "active",
  "created_at": "2024-06-15T10:30:00Z",
  "email": "alice@example.com",
  "total_amount": 299.99
}

Generated SQL

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  email VARCHAR(255) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_email ON orders (email);

Which Columns Get Indexes

The converter generates indexes for:

1. Foreign keys — Columns ending in _id (essential for JOIN performance).
2. Status/state columns — Low-cardinality columns used in WHERE clauses.
3. Timestamp columns — Frequently used for date-range queries and sorting.
4. Email/username — Columns commonly used in lookups.

Index Types

Type	Engine	Use case
B-tree (default)	All	General purpose, range queries, sorting
Hash	PostgreSQL, MySQL	Exact equality lookups only
GIN	PostgreSQL	Full-text search, JSONB, arrays
GiST	PostgreSQL	Geometric, range types, nearest-neighbor

Composite Indexes

For queries that filter on multiple columns, a composite index is more efficient:

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

Column order matters — put the most selective column first, and the column used in range conditions last.

Partial Indexes

CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

This smaller index only covers active orders, making it faster and using less disk space.

Over-Indexing Warning

Every index slows down INSERT, UPDATE, and DELETE operations and consumes storage. The converter adds a comment warning when it generates more than 5 indexes for a single table — a sign that you should review and consolidate.

Naming Convention

Use idx_{table}_{column(s)} for regular indexes and uq_{table}_{column(s)} for unique indexes. Consistent naming makes maintenance and debugging significantly easier.