Skip to main content
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
DatabaseOutput
PostgreSQLCREATE INDEX idx_email ON users (email)
MySQLCREATE INDEX idx_email ON users (email)

​
Unique Index

:CREATE INDEX User idx_email:email UNIQUE
DatabaseOutput
PostgreSQLCREATE UNIQUE INDEX idx_email ON users (email)
MySQLCREATE UNIQUE INDEX idx_email ON users (email)

​
Drop Index

:DROP INDEX User idx_email
DatabaseOutput
PostgreSQLDROP INDEX IF EXISTS idx_email
MySQLDROP INDEX idx_email ON users

​
When to Use Indexes

Use CaseRecommendation
Frequent WHERE clausesIndex the filtered column
Unique constraintsUse UNIQUE index
Foreign keysIndex the reference column
SortingIndex 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

FeaturePostgreSQLMySQLMongoDB
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