Indexes

Indexes speed up data retrieval at the cost of slower writes.

Basic Syntax

:CREATE INDEX Entity index_name:column
:CREATE INDEX Entity index_name:column UNIQUE
:DROP INDEX Entity index_name

Create Index

:CREATE INDEX User idx_email:email

Database	Output
PostgreSQL	`CREATE INDEX idx_email ON users (email)`
MySQL	`CREATE INDEX idx_email ON users (email)`

Unique Index

:CREATE INDEX User idx_email:email UNIQUE

Database	Output
PostgreSQL	`CREATE UNIQUE INDEX idx_email ON users (email)`
MySQL	`CREATE UNIQUE INDEX idx_email ON users (email)`

Drop Index

:DROP INDEX User idx_email

Database	Output
PostgreSQL	`DROP INDEX IF EXISTS idx_email`
MySQL	`DROP INDEX idx_email ON users`

When to Use Indexes

Use Case	Recommendation
Frequent WHERE clauses	Index the filtered column
Unique constraints	Use UNIQUE index
Foreign keys	Index the reference column
Sorting	Index the ORDER BY column

Index Best Practices

Do:

Index columns used in WHERE clauses
Index columns used in JOIN conditions
Index columns used in ORDER BY
Use unique indexes for email, username, etc.

Don’t:

Over-index (slows down writes)
Index rarely queried columns
Index very small tables

Complete Examples

User Table

:CREATE INDEX User idx_email:email UNIQUE
:CREATE INDEX User idx_status:status
:CREATE INDEX User idx_created:created_at

Order Table

:CREATE INDEX Order idx_user:user_id
:CREATE INDEX Order idx_status:status
:CREATE INDEX Order idx_created:created_at

Database Support

Feature	PostgreSQL	MySQL	MongoDB
Single column index	✅	✅	Via driver
UNIQUE modifier	✅	✅	Via driver
DROP INDEX	✅	✅	Via driver

For MongoDB indexes, use native driver methods.

Limitations

Current index implementation supports:

Single column indexes
UNIQUE constraint

For advanced indexes (composite, partial, full-text, GIN), use native SQL.

Next Steps

Views

Create virtual tables

Transactions

Group operations safely

Getting Started

Queries

Mutations

Schema

Control

Reference

Databases

Integration

Basic Syntax

Create Index

Unique Index

Drop Index

When to Use Indexes

Index Best Practices

Complete Examples

User Table

Order Table

Database Support

Limitations

Next Steps

Views

Transactions

​Basic Syntax

​Create Index

​Unique Index

​Drop Index

​When to Use Indexes

​Index Best Practices

​Complete Examples

​User Table

​Order Table

​Database Support

​Limitations

​Next Steps

Views

Transactions

Basic Syntax

Create Index

Unique Index

Drop Index

When to Use Indexes

Index Best Practices

Complete Examples

User Table

Order Table

Database Support

Limitations

Next Steps