DbCli Index Management Skill

Command Style (Use PATH)

All examples use the plain command name dbcli (no directory prefix). Ensure dbcli is on PATH instead of hardcoding paths like .\.claude\skills\dbcli\dbcli.exe.

Overview

Manage database indexes using DbCli's DDL command. This skill covers creating, analyzing, and dropping indexes to optimize query performance across 30+ database systems.

Safety Level: Moderate
Requires Backup: Recommended before dropping indexes on production

Quick Start

bash

# ⚠️ IMPORTANT: Backup indexes before DROP operations
# Backup all indexes
export DBCLI_CONNECTION="Data Source=app.db"
dbcli export-schema index -o backup_indexes.sql

# Create an index
dbcli ddl "CREATE INDEX idx_users_email ON Users(email)"

# Create a unique index
dbcli ddl "CREATE UNIQUE INDEX idx_users_username ON Users(username)"

# Drop an index (with backup)
dbcli ddl "DROP INDEX idx_old_index"

# Restore from backup if needed
# Execute the CREATE INDEX statement from backup_indexes.sql

# List all indexes
dbcli query "SELECT name FROM sqlite_master WHERE type='index'" -f table

Command Syntax

bash

dbcli [options] ddl "<INDEX_DDL_STATEMENT>"

Options

Option	Alias	Description	Example
`--db-type`	`-t`	Database type	`-t sqlserver`, `-t mysql`
`--format`	`-f`	Output format	`-f json`, `-f table`
`--file`	`-F`	Read SQL from file	`-F create_indexes.sql`
`--config`		Use config file	`--config <path>`

Core Operations

1. Create Single-Column Index

SQLite

bash

# Simple index
export DBCLI_CONNECTION="Data Source=app.db"
dbcli ddl "CREATE INDEX idx_users_email ON Users(email)"

# Unique index
dbcli ddl "CREATE UNIQUE INDEX idx_users_username ON Users(username)"

# Index with IF NOT EXISTS
dbcli ddl "CREATE INDEX IF NOT EXISTS idx_users_status ON Users(status)"

SQL Server

bash

# Nonclustered index
export DBCLI_CONNECTION="Server=.;Database=mydb;Trusted_Connection=True"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE NONCLUSTERED INDEX IX_Customers_Email 
ON dbo.Customers(Email)"

# Unique index
dbcli ddl "
CREATE UNIQUE INDEX IX_Products_SKU 
ON dbo.Products(SKU)"

# Clustered index
dbcli ddl "
CREATE CLUSTERED INDEX IX_Orders_OrderDate 
ON dbo.Orders(OrderDate)"

MySQL

bash

# Simple index
export DBCLI_CONNECTION="Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx"
export DBCLI_DBTYPE="mysql"
dbcli ddl "
CREATE INDEX idx_users_email ON Users(email)"

# Unique index
dbcli ddl "
CREATE UNIQUE INDEX idx_users_username ON Users(username)"

# FULLTEXT index (MyISAM/InnoDB)
dbcli ddl "
CREATE FULLTEXT INDEX idx_articles_content ON Articles(content)"

PostgreSQL

bash

# B-tree index (default)
export DBCLI_CONNECTION="Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
CREATE INDEX idx_users_email ON users(email)"

# Unique index
dbcli ddl "
CREATE UNIQUE INDEX idx_users_username ON users(username)"

# Partial index
dbcli ddl "
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'"

# Index with specific method
dbcli ddl "
CREATE INDEX idx_users_name_gin ON users USING gin(to_tsvector('english', name))"

Oracle

bash

# Simple index
export DBCLI_CONNECTION="Data Source=orcl;User Id=system;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="oracle"
dbcli ddl "
CREATE INDEX idx_employees_email ON employees(email)"

# Unique index
dbcli ddl "
CREATE UNIQUE INDEX idx_employees_emp_no ON employees(employee_number)"

# Function-based index
dbcli ddl "
CREATE INDEX idx_employees_upper_name ON employees(UPPER(name))"

DaMeng

bash

export DBCLI_CONNECTION="Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb"
export DBCLI_DBTYPE="dm"
dbcli ddl "
CREATE INDEX idx_orders_customer ON orders(customer_id)"

# Unique index
dbcli ddl "
CREATE UNIQUE INDEX idx_products_code ON products(product_code)"

GaussDB

bash

export DBCLI_CONNECTION="Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="gaussdb"
dbcli ddl "
CREATE INDEX idx_sales_date ON sales(sale_date)"

# Partial index
dbcli ddl "
CREATE INDEX idx_high_value_sales ON sales(amount) WHERE amount > 1000"

KingbaseES

bash

export DBCLI_CONNECTION="Server=localhost;Port=54321;Database=mydb;UID=system;PWD=xxxxxxxxxx"
export DBCLI_DBTYPE="kdbndp"
dbcli ddl "
CREATE INDEX idx_inventory_product ON inventory(product_id)"

# Unique index
dbcli ddl "
CREATE UNIQUE INDEX idx_inventory_sku ON inventory(sku)"

2. Create Composite Index (Multiple Columns)

SQLite

bash

export DBCLI_CONNECTION="Data Source=app.db"
dbcli ddl "
CREATE INDEX idx_users_status_created ON Users(status, created_at)"

SQL Server

bash

export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE INDEX IX_Orders_CustomerDate 
ON dbo.Orders(CustomerID, OrderDate) 
INCLUDE (TotalAmount)"

MySQL

bash

export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli ddl "
CREATE INDEX idx_orders_customer_date ON Orders(customer_id, order_date)"

PostgreSQL

bash

export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC)"

Oracle

bash

export DBCLI_CONNECTION="Data Source=orcl"
export DBCLI_DBTYPE="oracle"
dbcli ddl "
CREATE INDEX idx_orders_comp ON orders(customer_id, order_date, status)"

3. Create Filtered/Partial Index

SQL Server (Filtered Index)

bash

export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE INDEX IX_Orders_Active 
ON dbo.Orders(OrderDate) 
WHERE Status = 'Active'"

PostgreSQL (Partial Index)

bash

export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
CREATE INDEX idx_pending_orders 
ON orders(created_at) 
WHERE status = 'pending'"

Oracle (Function-Based Index)

bash

export DBCLI_CONNECTION="Data Source=orcl"
export DBCLI_DBTYPE="oracle"
dbcli ddl "
CREATE INDEX idx_active_orders 
ON orders(order_date) 
WHERE status = 'ACTIVE'"

4. Create Full-Text Index

SQL Server

bash

# Create full-text catalog (required first)
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT"

# Create full-text index
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE FULLTEXT INDEX ON dbo.Articles(content) 
KEY INDEX PK_Articles"

MySQL

bash

export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli ddl "
CREATE FULLTEXT INDEX idx_articles_fulltext 
ON Articles(title, content)"

PostgreSQL (GIN Index for Full-Text)

bash

export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
CREATE INDEX idx_documents_fulltext 
ON documents 
USING gin(to_tsvector('english', content))"

5. Drop Index

⚠️ WARNING: Dropping indexes can severely impact query performance. Analyze queries first!

SQLite

bash

export DBCLI_CONNECTION="Data Source=app.db"
dbcli ddl "DROP INDEX IF EXISTS idx_old_index"

SQL Server

bash

export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
DROP INDEX IF EXISTS IX_OldIndex ON dbo.TableName"

MySQL

bash

export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli ddl "
DROP INDEX idx_old_index ON TableName"

PostgreSQL

bash

export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
DROP INDEX IF EXISTS idx_old_index CASCADE"

Oracle

bash

export DBCLI_CONNECTION="Data Source=orcl"
export DBCLI_DBTYPE="oracle"
dbcli ddl "
DROP INDEX idx_old_index"

6. List Indexes

SQLite

bash

# All indexes
export DBCLI_CONNECTION="Data Source=app.db"
dbcli query "
SELECT name, tbl_name, sql 
FROM sqlite_master 
WHERE type='index' 
ORDER BY tbl_name, name" -f table

# Indexes for specific table
export DBCLI_CONNECTION="Data Source=app.db"
dbcli query "
PRAGMA index_list('Users')" -f table

# Index details
export DBCLI_CONNECTION="Data Source=app.db"
dbcli query "
PRAGMA index_info('idx_users_email')" -f table

SQL Server

bash

# All indexes in database
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli query "
SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key
FROM sys.indexes i
WHERE i.object_id > 100
ORDER BY schema_name, table_name, index_name" -f table

# Indexes for specific table
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli query "
SELECT 
    i.name AS index_name,
    COL_NAME(ic.object_id, ic.column_id) AS column_name,
    ic.index_column_id,
    ic.is_included_column
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('dbo.Users')
ORDER BY i.name, ic.index_column_id" -f table

# Index usage statistics
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli query "
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY s.user_seeks DESC" -f table

MySQL

bash

# All indexes in database
export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli query "
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX" -f table

# Indexes for specific table
export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli query "
SHOW INDEX FROM Users" -f table

PostgreSQL

bash

# All indexes in schema
export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli query "
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname" -f table

# Indexes for specific table
export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli query "
SELECT
    i.relname AS index_name,
    a.attname AS column_name,
    am.amname AS index_type,
    ix.indisunique AS is_unique,
    ix.indisprimary AS is_primary
FROM pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_attribute a ON a.attrelid = t.oid
JOIN pg_am am ON i.relam = am.oid
WHERE t.relname = 'users'
    AND a.attnum = ANY(ix.indkey)
ORDER BY i.relname, a.attnum" -f table

# Index size
export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli query "
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC" -f table

Oracle

bash

# All indexes for user
export DBCLI_CONNECTION="Data Source=orcl"
export DBCLI_DBTYPE="oracle"
dbcli query "
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
ORDER BY table_name, index_name" -f table

# Index columns
export DBCLI_CONNECTION="Data Source=orcl"
export DBCLI_DBTYPE="oracle"
dbcli query "
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position" -f table

7. Analyze Index Performance

SQL Server (Missing Index Suggestions)

bash

export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli query "
SELECT 
    CONVERT(decimal(28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX idx_' + CONVERT(varchar, mig.index_group_handle) + '_' + 
    CONVERT(varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + 
    ISNULL(mid.equality_columns,'') + 
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + 
    ISNULL(mid.inequality_columns, '') + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) > 10
ORDER BY improvement_measure DESC" -f table

PostgreSQL (Unused Indexes)

bash

export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli query "
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC" -f table

MySQL (Index Cardinality)

bash

export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli query "
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
    AND CARDINALITY IS NOT NULL
ORDER BY TABLE_NAME, INDEX_NAME" -f table

Code Integration Examples

Node.js / JavaScript

javascript

const { execSync } = require('child_process');

// Create index
function createIndex(table, column, indexName = null) {
  const name = indexName || `idx_${table}_${column}`;
  process.env.DBCLI_CONNECTION = "Data Source=app.db";
  execSync(`dbcli ddl "CREATE INDEX ${name} ON ${table}(${column})"`);
  console.log(`✅ Index ${name} created`);
}

// Create composite index
function createCompositeIndex(table, columns, indexName = null) {
  const columnList = columns.join(', ');
  const name = indexName || `idx_${table}_${columns.join('_')}`;
  process.env.DBCLI_CONNECTION = "Data Source=app.db";
  execSync(`dbcli ddl "CREATE INDEX ${name} ON ${table}(${columnList})"`);
  console.log(`✅ Composite index ${name} created`);
}

// Drop index
function dropIndex(indexName) {
  process.env.DBCLI_CONNECTION = "Data Source=app.db";
  execSync(`dbcli ddl "DROP INDEX IF EXISTS ${indexName}"`);
  console.log(`✅ Index ${indexName} dropped`);
}

// List all indexes
function listIndexes() {
  process.env.DBCLI_CONNECTION = "Data Source=app.db";
  const result = JSON.parse(
    execSync('dbcli query "SELECT name, tbl_name FROM sqlite_master WHERE type=\'index\'"').toString()
  );
  return result;
}

// Get indexes for specific table
function getTableIndexes(tableName) {
  process.env.DBCLI_CONNECTION = "Data Source=app.db";
  const result = JSON.parse(
    execSync(`dbcli query "PRAGMA index_list('${tableName}')" `).toString()
  );
  return result;
}

// Usage
createIndex('Users', 'email');
createCompositeIndex('Orders', ['customer_id', 'order_date']);
console.log('All indexes:', listIndexes());
console.log('Users indexes:', getTableIndexes('Users'));
dropIndex('idx_old_index');

Python

python

import subprocess
import json

def create_index(table: str, column: str, index_name: str = None, unique: bool = False):
    """Create a database index"""
    name = index_name or f"idx_{table}_{column}"
    unique_clause = "UNIQUE " if unique else ""
    sql = f"CREATE {unique_clause}INDEX {name} ON {table}({column})"
    import os
    os.environ['DBCLI_CONNECTION'] = 'Data Source=app.db'
    subprocess.run(['dbcli', 'ddl', sql], check=True)
    print(f"✅ Index {name} created")

def create_composite_index(table: str, columns: list, index_name: str = None):
    """Create a composite index"""
    column_list = ', '.join(columns)
    name = index_name or f"idx_{table}_{'_'.join(columns)}"
    sql = f"CREATE INDEX {name} ON {table}({column_list})"
    import os
    os.environ['DBCLI_CONNECTION'] = 'Data Source=app.db'
    subprocess.run(['dbcli', 'ddl', sql], check=True)
    print(f"✅ Composite index {name} created")

def drop_index(index_name: str):
    """Drop an index"""
    sql = f"DROP INDEX IF EXISTS {index_name}"
    import os
    os.environ['DBCLI_CONNECTION'] = 'Data Source=app.db'
    subprocess.run(['dbcli', 'ddl', sql], check=True)
    print(f"✅ Index {index_name} dropped")

def list_indexes():
    """Get all indexes in database"""
    import os
    os.environ['DBCLI_CONNECTION'] = 'Data Source=app.db'
    result = subprocess.run(
        ['dbcli', 'query',
         "SELECT name, tbl_name FROM sqlite_master WHERE type='index'"],
        capture_output=True, text=True, check=True
    )
    return json.loads(result.stdout)

def get_table_indexes(table_name: str):
    """Get indexes for specific table"""
    import os
    os.environ['DBCLI_CONNECTION'] = 'Data Source=app.db'
    result = subprocess.run(
        ['dbcli', 'query',
         f"PRAGMA index_list('{table_name}')"],
        capture_output=True, text=True, check=True
    )
    return json.loads(result.stdout)

# Usage
create_index('Users', 'email', unique=True)
create_composite_index('Orders', ['customer_id', 'order_date'])
print('All indexes:', list_indexes())
print('Users indexes:', get_table_indexes('Users'))
drop_index('idx_old_index')

PowerShell

powershell

# Create index
function New-DbIndex {
    param(
        [string]$Table,
        [string]$Column,
        [string]$IndexName = $null,
        [switch]$Unique
    )
    
    $name = if ($IndexName) { $IndexName } else { "idx_${Table}_${Column}" }
    $uniqueClause = if ($Unique) { "UNIQUE " } else { "" }
    $sql = "CREATE ${uniqueClause}INDEX $name ON $Table($Column)"
    
    $env:DBCLI_CONNECTION = "Data Source=app.db"
    dbcli ddl $sql
    Write-Host "✅ Index $name created" -ForegroundColor Green
}

# Create composite index
function New-DbCompositeIndex {
    param(
        [string]$Table,
        [string[]]$Columns,
        [string]$IndexName = $null
    )
    
    $columnList = $Columns -join ', '
    $name = if ($IndexName) { $IndexName } else { "idx_${Table}_$($Columns -join '_')" }
    $sql = "CREATE INDEX $name ON $Table($columnList)"
    
    $env:DBCLI_CONNECTION = "Data Source=app.db"
    dbcli ddl $sql
    Write-Host "✅ Composite index $name created" -ForegroundColor Green
}

# Drop index
function Remove-DbIndex {
    param([string]$IndexName)
    
    $env:DBCLI_CONNECTION = "Data Source=app.db"
    dbcli ddl "DROP INDEX IF EXISTS $IndexName"
    Write-Host "✅ Index $IndexName dropped" -ForegroundColor Green
}

# List all indexes
function Get-DbIndexes {
    $env:DBCLI_CONNECTION = "Data Source=app.db"
    $result = dbcli query "SELECT name, tbl_name FROM sqlite_master WHERE type='index'" | ConvertFrom-Json
    return $result
}

# Get table indexes
function Get-DbTableIndexes {
    param([string]$TableName)
    
    $env:DBCLI_CONNECTION = "Data Source=app.db"
    $result = dbcli query "PRAGMA index_list('$TableName')" | ConvertFrom-Json
    return $result
}

# Usage
New-DbIndex -Table "Users" -Column "email" -Unique
New-DbCompositeIndex -Table "Orders" -Columns @("customer_id", "order_date")
Get-DbIndexes
Get-DbTableIndexes -TableName "Users"
Remove-DbIndex -IndexName "idx_old_index"

Best Practices

✅ When to Create Indexes

Columns in WHERE clauses:

sql

SELECT * FROM Orders WHERE customer_id = 123  -- Index customer_id

Columns in JOIN conditions:

sql

SELECT * FROM Orders o JOIN Customers c ON o.customer_id = c.id  -- Index customer_id

Columns in ORDER BY:

sql

SELECT * FROM Orders ORDER BY order_date DESC  -- Index order_date

Foreign key columns:

sql

CREATE INDEX idx_orders_customer ON Orders(customer_id)

Columns used in GROUP BY:

sql

SELECT customer_id, COUNT(*) FROM Orders GROUP BY customer_id  -- Index customer_id

⚠️ When NOT to Create Indexes

Small tables (< 1000 rows) - full table scan is faster
Frequently updated columns - index maintenance overhead
Low cardinality columns - columns with few distinct values
Wide columns - large text or binary data

🎯 Index Naming Conventions

idx_<table>_<column1>[_<column2>]       # General index
pk_<table>                               # Primary key
uk_<table>_<column>                      # Unique constraint
fk_<table1>_<table2>                     # Foreign key

📊 Monitor Index Usage

Before dropping an index, check if it's being used:

bash

# SQL Server - Check index usage
dbcli query "
SELECT 
    i.name,
    s.user_seeks + s.user_scans + s.user_lookups AS reads,
    s.user_updates AS writes
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECT_NAME(i.object_id) = 'YourTable'" -t sqlserver

# PostgreSQL - Check index usage
dbcli query "
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'" -t postgresql

Advanced Patterns

Covering Indexes (SQL Server)

bash

# Index with INCLUDE columns (non-key columns)
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE INDEX IX_Orders_Customer 
ON Orders(customer_id, order_date) 
INCLUDE (total_amount, status)"

Filtered Indexes

bash

# SQL Server - Index only active records
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
CREATE INDEX IX_Active_Orders 
ON Orders(order_date) 
WHERE status = 'active'"

# PostgreSQL - Partial index
export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
CREATE INDEX idx_pending_orders 
ON orders(created_at) 
WHERE status = 'pending'"

Index Maintenance

SQL Server - Rebuild Index

bash

export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
ALTER INDEX IX_Orders_Customer ON Orders REBUILD"

SQL Server - Reorganize Index

bash

export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli ddl "
ALTER INDEX IX_Orders_Customer ON Orders REORGANIZE"

PostgreSQL - Rebuild Index

bash

export DBCLI_CONNECTION="Host=localhost;Database=mydb"
export DBCLI_DBTYPE="postgresql"
dbcli ddl "
REINDEX INDEX idx_orders_customer"

MySQL - Rebuild Table Indexes

bash

export DBCLI_CONNECTION="Server=localhost;Database=mydb"
export DBCLI_DBTYPE="mysql"
dbcli ddl "
OPTIMIZE TABLE Orders"

Troubleshooting

Common Errors

"Index already exists"

bash

# Use IF NOT EXISTS
dbcli ddl "CREATE INDEX IF NOT EXISTS idx_name ON table(column)"

"Cannot create index on this column type"

bash

# Some databases don't support indexing TEXT/BLOB columns directly
# Use a computed column or full-text index instead

"Unique constraint violation"

bash

# Check for duplicate values before creating unique index
dbcli query "SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1"

Performance Tips

Create indexes during off-peak hours - index creation locks tables
Use composite indexes wisely - column order matters!
Monitor index fragmentation - rebuild fragmented indexes
Remove unused indexes - they slow down INSERT/UPDATE/DELETE
Use covering indexes - include SELECT columns in index
Consider partitioning - for very large tables

Related Skills

dbcli-query - Analyze query performance
dbcli-db-ddl - Create tables with indexes
dbcli-view - Manage views
dbcli-procedure - Optimize stored procedures

Database-Specific Index Types

Database	B-Tree	Hash	GiST	GIN	BRIN	Full-Text
SQLite	✅	❌	❌	❌	❌	❌
SQL Server	✅ (Clustered/Nonclustered)	✅	❌	❌	❌	✅
MySQL	✅	✅	❌	❌	❌	✅
PostgreSQL	✅	✅	✅	✅	✅	✅ (GIN)
Oracle	✅	❌	❌	❌	❌	✅ (Oracle Text)

Connection String Reference

See CONNECTION_STRINGS.md for 30+ database connection string examples.

Support

For issues and questions:

GitHub Issues: https://github.com/your-repo/dbcli/issues
Documentation: https://github.com/your-repo/dbcli

License: MIT
Version: 1.0.0

Search AI Tools

Install this agent skill to your Project

Metadata

SKILL.md

DbCli Index Management Skill

Command Style (Use PATH)

Overview

Quick Start

Command Syntax

Options

Core Operations

1. Create Single-Column Index

SQLite

SQL Server

MySQL

PostgreSQL

Oracle

DaMeng

GaussDB

KingbaseES

2. Create Composite Index (Multiple Columns)

SQLite

SQL Server

MySQL

PostgreSQL

Oracle

3. Create Filtered/Partial Index

SQL Server (Filtered Index)

PostgreSQL (Partial Index)

Oracle (Function-Based Index)

4. Create Full-Text Index

SQL Server

MySQL

PostgreSQL (GIN Index for Full-Text)

5. Drop Index

SQLite

SQL Server

MySQL

PostgreSQL

Oracle

6. List Indexes

SQLite

SQL Server

MySQL

PostgreSQL

Oracle

7. Analyze Index Performance

SQL Server (Missing Index Suggestions)

PostgreSQL (Unused Indexes)

MySQL (Index Cardinality)

Code Integration Examples

Node.js / JavaScript

Python

PowerShell

Best Practices

✅ When to Create Indexes

⚠️ When NOT to Create Indexes

🎯 Index Naming Conventions

📊 Monitor Index Usage

Advanced Patterns

Covering Indexes (SQL Server)

Filtered Indexes

Index Maintenance

SQL Server - Rebuild Index

SQL Server - Reorganize Index

PostgreSQL - Rebuild Index

MySQL - Rebuild Table Indexes

Troubleshooting

Common Errors

"Index already exists"

"Cannot create index on this column type"

"Unique constraint violation"

Performance Tips

Related Skills

Database-Specific Index Types

Connection String Reference

Support