Skip to main content

​
Indexing

Optimize MatsushibaDB query performance with strategic indexing. Learn to create, manage, and optimize indexes for maximum efficiency.

​
Index Fundamentals

​
What are Indexes?

Indexes are data structures that improve the speed of data retrieval operations on a database table. They work like a book’s index, providing quick access to specific data without scanning the entire table. 
-- Basic index creation
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Unique indexes
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite indexes
CREATE INDEX idx_posts_author_date ON posts(author_id, created_at);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status, created_at);

​
Index Types

-- Single-column indexes
CREATE INDEX idx_single_column ON table_name(column_name);

-- Composite indexes (multi-column)
CREATE INDEX idx_composite ON table_name(col1, col2, col3);

-- Partial indexes (with WHERE clause)
CREATE INDEX idx_partial ON table_name(column_name) WHERE condition;

-- Covering indexes (include additional columns)
CREATE INDEX idx_covering ON table_name(col1, col2) INCLUDE (col3, col4);

-- Expression indexes
CREATE INDEX idx_expression ON table_name(UPPER(column_name));
CREATE INDEX idx_date_part ON table_name(strftime('%Y-%m', date_column));

​
Index Creation Strategies

​
Single-Column Indexes

// Create single-column indexes
function createSingleColumnIndexes() {
    const indexes = [
        { table: 'users', column: 'email', unique: true },
        { table: 'users', column: 'created_at', unique: false },
        { table: 'posts', column: 'author_id', unique: false },
        { table: 'posts', column: 'published', unique: false },
        { table: 'orders', column: 'customer_id', unique: false },
        { table: 'orders', column: 'status', unique: false }
    ];
    
    indexes.forEach(({ table, column, unique }) => {
        const indexName = `idx_${table}_${column}`;
        const uniqueClause = unique ? 'UNIQUE ' : '';
        
        try {
            db.run(`CREATE ${uniqueClause}INDEX ${indexName} ON ${table}(${column})`);
            console.log(`Created ${uniqueClause}index: ${indexName}`);
        } catch (error) {
            if (error.message.includes('already exists')) {
                console.log(`Index ${indexName} already exists`);
            } else {
                console.error(`Error creating index ${indexName}:`, error.message);
            }
        }
    });
}

// Analyze index usage
function analyzeIndexUsage() {
    const indexStats = db.all(`
        SELECT 
            name,
            tbl_name,
            sql,
            CASE 
                WHEN name LIKE 'sqlite_autoindex_%' THEN 'auto'
                ELSE 'manual'
            END as index_type
        FROM sqlite_master 
        WHERE type = 'index' 
        AND name NOT LIKE 'sqlite_%'
        ORDER BY tbl_name, name
    `);
    
    console.log('Index Statistics:', indexStats);
    return indexStats;
}

​
Composite Indexes

// Create composite indexes
function createCompositeIndexes() {
    const compositeIndexes = [
        {
            name: 'idx_posts_author_published',
            table: 'posts',
            columns: ['author_id', 'published', 'created_at'],
            description: 'Optimize queries filtering by author and publication status'
        },
        {
            name: 'idx_orders_customer_status_date',
            table: 'orders',
            columns: ['customer_id', 'status', 'created_at'],
            description: 'Optimize order queries by customer and status'
        },
        {
            name: 'idx_users_status_created',
            table: 'users',
            columns: ['status', 'created_at'],
            description: 'Optimize user queries by status and creation date'
        }
    ];
    
    compositeIndexes.forEach(({ name, table, columns, description }) => {
        try {
            const columnList = columns.join(', ');
            db.run(`CREATE INDEX ${name} ON ${table}(${columnList})`);
            console.log(`Created composite index: ${name} - ${description}`);
        } catch (error) {
            if (error.message.includes('already exists')) {
                console.log(`Composite index ${name} already exists`);
            } else {
                console.error(`Error creating composite index ${name}:`, error.message);
            }
        }
    });
}

// Analyze composite index effectiveness
function analyzeCompositeIndex(query) {
    try {
        const explainQuery = `EXPLAIN QUERY PLAN ${query}`;
        const plan = db.all(explainQuery);
        
        console.log('Query Plan:', plan);
        
        // Check if composite index is being used
        const usesCompositeIndex = plan.some(step => 
            step.detail.includes('USING INDEX') && 
            step.detail.includes('idx_')
        );
        
        return {
            query: query,
            plan: plan,
            usesCompositeIndex: usesCompositeIndex,
            recommendations: generateIndexRecommendations(plan)
        };
    } catch (error) {
        console.error('Error analyzing query:', error);
        return null;
    }
}

​
Partial Indexes

// Create partial indexes
function createPartialIndexes() {
    const partialIndexes = [
        {
            name: 'idx_active_users',
            table: 'users',
            column: 'email',
            condition: "status = 'active'",
            description: 'Index only active users for faster lookups'
        },
        {
            name: 'idx_published_posts',
            table: 'posts',
            column: 'created_at',
            condition: 'published = 1',
            description: 'Index only published posts by creation date'
        },
        {
            name: 'idx_recent_orders',
            table: 'orders',
            column: 'customer_id',
            condition: "created_at > date('now', '-30 days')",
            description: 'Index recent orders for better performance'
        },
        {
            name: 'idx_high_value_orders',
            table: 'orders',
            column: 'status',
            condition: 'total_amount > 1000',
            description: 'Index high-value orders for priority processing'
        }
    ];
    
    partialIndexes.forEach(({ name, table, column, condition, description }) => {
        try {
            db.run(`CREATE INDEX ${name} ON ${table}(${column}) WHERE ${condition}`);
            console.log(`Created partial index: ${name} - ${description}`);
        } catch (error) {
            if (error.message.includes('already exists')) {
                console.log(`Partial index ${name} already exists`);
            } else {
                console.error(`Error creating partial index ${name}:`, error.message);
            }
        }
    });
}

// Analyze partial index usage
function analyzePartialIndexUsage() {
    const partialIndexStats = db.all(`
        SELECT 
            name,
            tbl_name,
            sql,
            CASE 
                WHEN sql LIKE '%WHERE%' THEN 'partial'
                ELSE 'full'
            END as index_type
        FROM sqlite_master 
        WHERE type = 'index' 
        AND name NOT LIKE 'sqlite_%'
        AND sql LIKE '%WHERE%'
        ORDER BY tbl_name, name
    `);
    
    console.log('Partial Index Statistics:', partialIndexStats);
    return partialIndexStats;
}

​
Index Optimization

​
Query Analysis

// Query performance analyzer
class QueryAnalyzer {
    constructor(db) {
        this.db = db;
        this.queryHistory = [];
    }
    
    // Analyze query performance
    analyzeQuery(sql, params = []) {
        const startTime = Date.now();
        
        try {
            // Get query plan
            const explainQuery = `EXPLAIN QUERY PLAN ${sql}`;
            const plan = this.db.all(explainQuery, params);
            
            // Execute query to measure performance
            const result = this.db.all(sql, params);
            const executionTime = Date.now() - startTime;
            
            // Analyze plan
            const analysis = this.analyzeQueryPlan(plan);
            
            // Store query history
            this.queryHistory.push({
                sql: sql,
                params: params,
                executionTime: executionTime,
                plan: plan,
                analysis: analysis,
                timestamp: new Date()
            });
            
            return {
                sql: sql,
                executionTime: executionTime,
                resultCount: result.length,
                plan: plan,
                analysis: analysis,
                recommendations: this.generateRecommendations(analysis)
            };
        } catch (error) {
            console.error('Query analysis failed:', error);
            return null;
        }
    }
    
    // Analyze query plan
    analyzeQueryPlan(plan) {
        const analysis = {
            hasTableScan: false,
            usesIndex: false,
            indexName: null,
            estimatedRows: 0,
            operations: []
        };
        
        plan.forEach(step => {
            const detail = step.detail;
            
            if (detail.includes('SCAN TABLE') && !detail.includes('USING INDEX')) {
                analysis.hasTableScan = true;
            }
            
            if (detail.includes('USING INDEX')) {
                analysis.usesIndex = true;
                const indexMatch = detail.match(/USING INDEX (\w+)/);
                if (indexMatch) {
                    analysis.indexName = indexMatch[1];
                }
            }
            
            // Extract operation type
            if (detail.includes('SCAN TABLE')) {
                analysis.operations.push('TABLE_SCAN');
            } else if (detail.includes('SEARCH TABLE')) {
                analysis.operations.push('INDEX_SEARCH');
            } else if (detail.includes('USE TEMP B-TREE')) {
                analysis.operations.push('TEMP_BTREE');
            }
        });
        
        return analysis;
    }
    
    // Generate optimization recommendations
    generateRecommendations(analysis) {
        const recommendations = [];
        
        if (analysis.hasTableScan) {
            recommendations.push({
                type: 'missing_index',
                severity: 'high',
                message: 'Query performs full table scan. Consider adding an index.',
                suggestion: 'Create an index on the columns used in WHERE clause'
            });
        }
        
        if (analysis.operations.includes('TEMP_BTREE')) {
            recommendations.push({
                type: 'temp_btree',
                severity: 'medium',
                message: 'Query uses temporary B-tree for sorting/grouping.',
                suggestion: 'Consider adding an index to avoid temporary sorting'
            });
        }
        
        if (analysis.executionTime > 1000) {
            recommendations.push({
                type: 'slow_query',
                severity: 'high',
                message: 'Query execution time is slow.',
                suggestion: 'Optimize query or add appropriate indexes'
            });
        }
        
        return recommendations;
    }
    
    // Get slow queries
    getSlowQueries(threshold = 1000) {
        return this.queryHistory.filter(query => 
            query.executionTime > threshold
        ).sort((a, b) => b.executionTime - a.executionTime);
    }
    
    // Get query statistics
    getQueryStats() {
        const totalQueries = this.queryHistory.length;
        const totalTime = this.queryHistory.reduce((sum, query) => sum + query.executionTime, 0);
        const averageTime = totalQueries > 0 ? totalTime / totalQueries : 0;
        
        return {
            totalQueries: totalQueries,
            totalTime: totalTime,
            averageTime: averageTime,
            slowQueries: this.getSlowQueries().length
        };
    }
}

// Usage
const analyzer = new QueryAnalyzer(db);

// Analyze a query
const analysis = analyzer.analyzeQuery(`
    SELECT * FROM users 
    WHERE status = ? AND created_at > ?
    ORDER BY created_at DESC
`, ['active', '2024-01-01']);

console.log('Query Analysis:', analysis);

​
Index Maintenance

// Index maintenance utilities
class IndexMaintenance {
    constructor(db) {
        this.db = db;
    }
    
    // Rebuild all indexes
    rebuildAllIndexes() {
        try {
            console.log('Starting index rebuild...');
            
            // Get all indexes
            const indexes = this.db.all(`
                SELECT name, sql, tbl_name
                FROM sqlite_master 
                WHERE type = 'index' 
                AND name NOT LIKE 'sqlite_%'
            `);
            
            indexes.forEach(({ name, sql, tbl_name }) => {
                try {
                    // Drop and recreate index
                    this.db.run(`DROP INDEX ${name}`);
                    this.db.run(sql);
                    console.log(`Rebuilt index: ${name} on table ${tbl_name}`);
                } catch (error) {
                    console.error(`Error rebuilding index ${name}:`, error.message);
                }
            });
            
            console.log('Index rebuild completed');
        } catch (error) {
            console.error('Error during index rebuild:', error.message);
        }
    }
    
    // Analyze table statistics
    analyzeTable(tableName) {
        try {
            const stats = this.db.get(`
                SELECT 
                    COUNT(*) as row_count,
                    COUNT(DISTINCT *) as unique_rows
                FROM ${tableName}
            `);
            
            const indexStats = this.db.all(`
                SELECT 
                    name,
                    sql,
                    CASE 
                        WHEN sql LIKE '%WHERE%' THEN 'partial'
                        ELSE 'full'
                    END as index_type
                FROM sqlite_master 
                WHERE type = 'index' 
                AND tbl_name = ?
                AND name NOT LIKE 'sqlite_%'
            `, [tableName]);
            
            return {
                table: tableName,
                rowCount: stats.row_count,
                uniqueRows: stats.unique_rows,
                indexes: indexStats
            };
        } catch (error) {
            console.error(`Error analyzing table ${tableName}:`, error.message);
            return null;
        }
    }
    
    // Check for unused indexes
    findUnusedIndexes() {
        try {
            // This is a simplified check - in practice, you'd need to monitor actual usage
            const allIndexes = this.db.all(`
                SELECT name, tbl_name, sql
                FROM sqlite_master 
                WHERE type = 'index' 
                AND name NOT LIKE 'sqlite_%'
            `);
            
            const unusedIndexes = [];
            
            allIndexes.forEach(({ name, tbl_name, sql }) => {
                // Check if index is likely unused based on naming patterns
                if (name.includes('temp_') || name.includes('old_')) {
                    unusedIndexes.push({
                        name: name,
                        table: tbl_name,
                        reason: 'Temporary or old index',
                        sql: sql
                    });
                }
            });
            
            return unusedIndexes;
        } catch (error) {
            console.error('Error finding unused indexes:', error.message);
            return [];
        }
    }
    
    // Optimize database
    optimizeDatabase() {
        try {
            console.log('Starting database optimization...');
            
            // Run ANALYZE to update statistics
            this.db.run('ANALYZE');
            console.log('Updated table statistics');
            
            // Run VACUUM to reclaim space
            this.db.run('VACUUM');
            console.log('Reclaimed database space');
            
            // Rebuild indexes
            this.rebuildAllIndexes();
            
            console.log('Database optimization completed');
        } catch (error) {
            console.error('Error during database optimization:', error.message);
        }
    }
}

// Usage
const maintenance = new IndexMaintenance(db);

// Analyze a table
const tableAnalysis = maintenance.analyzeTable('users');
console.log('Table Analysis:', tableAnalysis);

// Find unused indexes
const unusedIndexes = maintenance.findUnusedIndexes();
console.log('Unused Indexes:', unusedIndexes);

// Optimize database
maintenance.optimizeDatabase();

​
Index Best Practices

​
Index Design Guidelines

// Index design recommendations
class IndexDesigner {
    constructor(db) {
        this.db = db;
    }
    
    // Analyze table for index recommendations
    analyzeTableForIndexes(tableName) {
        try {
            // Get table structure
            const columns = this.db.all(`PRAGMA table_info(${tableName})`);
            
            // Get sample data to understand patterns
            const sampleData = this.db.all(`SELECT * FROM ${tableName} LIMIT 100`);
            
            const recommendations = [];
            
            columns.forEach(column => {
                const { name, type, pk, notnull } = column;
                
                // Primary key recommendations
                if (pk) {
                    recommendations.push({
                        type: 'primary_key',
                        column: name,
                        priority: 'high',
                        reason: 'Primary key automatically indexed',
                        action: 'No action needed'
                    });
                }
                
                // Foreign key recommendations
                if (name.endsWith('_id') && !pk) {
                    recommendations.push({
                        type: 'foreign_key',
                        column: name,
                        priority: 'high',
                        reason: 'Foreign key column for joins',
                        action: `CREATE INDEX idx_${tableName}_${name} ON ${tableName}(${name})`
                    });
                }
                
                // Unique column recommendations
                if (name.includes('email') || name.includes('username')) {
                    recommendations.push({
                        type: 'unique_column',
                        column: name,
                        priority: 'high',
                        reason: 'Unique identifier column',
                        action: `CREATE UNIQUE INDEX idx_${tableName}_${name} ON ${tableName}(${name})`
                    });
                }
                
                // Date/time column recommendations
                if (name.includes('created') || name.includes('updated') || name.includes('date')) {
                    recommendations.push({
                        type: 'datetime_column',
                        column: name,
                        priority: 'medium',
                        reason: 'Date/time column for sorting and filtering',
                        action: `CREATE INDEX idx_${tableName}_${name} ON ${tableName}(${name})`
                    });
                }
                
                // Status/flag column recommendations
                if (name.includes('status') || name.includes('active') || name.includes('published')) {
                    recommendations.push({
                        type: 'status_column',
                        column: name,
                        priority: 'medium',
                        reason: 'Status column for filtering',
                        action: `CREATE INDEX idx_${tableName}_${name} ON ${tableName}(${name})`
                    });
                }
            });
            
            return {
                table: tableName,
                columns: columns,
                recommendations: recommendations
            };
        } catch (error) {
            console.error(`Error analyzing table ${tableName}:`, error.message);
            return null;
        }
    }
    
    // Generate composite index recommendations
    generateCompositeIndexRecommendations(tableName) {
        try {
            // Analyze common query patterns
            const commonPatterns = [
                {
                    pattern: 'status + created_at',
                    columns: ['status', 'created_at'],
                    reason: 'Common filtering pattern'
                },
                {
                    pattern: 'user_id + status',
                    columns: ['user_id', 'status'],
                    reason: 'User-specific filtering'
                },
                {
                    pattern: 'category + published + created_at',
                    columns: ['category', 'published', 'created_at'],
                    reason: 'Content filtering and sorting'
                }
            ];
            
            const recommendations = [];
            
            commonPatterns.forEach(({ pattern, columns, reason }) => {
                // Check if columns exist in table
                const existingColumns = this.db.all(`PRAGMA table_info(${tableName})`);
                const columnNames = existingColumns.map(col => col.name);
                
                const matchingColumns = columns.filter(col => columnNames.includes(col));
                
                if (matchingColumns.length > 1) {
                    recommendations.push({
                        type: 'composite_index',
                        pattern: pattern,
                        columns: matchingColumns,
                        priority: 'medium',
                        reason: reason,
                        action: `CREATE INDEX idx_${tableName}_${matchingColumns.join('_')} ON ${tableName}(${matchingColumns.join(', ')})`
                    });
                }
            });
            
            return recommendations;
        } catch (error) {
            console.error(`Error generating composite index recommendations:`, error.message);
            return [];
        }
    }
}

// Usage
const designer = new IndexDesigner(db);

// Analyze table for indexes
const analysis = designer.analyzeTableForIndexes('users');
console.log('Index Analysis:', analysis);

// Generate composite index recommendations
const compositeRecommendations = designer.generateCompositeIndexRecommendations('posts');
console.log('Composite Index Recommendations:', compositeRecommendations);

​
Best Practices

1

Index Frequently Queried Columns

Create indexes on columns that appear frequently in WHERE clauses and JOIN conditions.
2

Use Composite Indexes Wisely

Create composite indexes for multi-column queries, but consider column order carefully.
3

Consider Partial Indexes

Use partial indexes for filtered data to reduce index size and improve performance.
4

Monitor Index Usage

Regularly monitor index usage and remove unused indexes to reduce maintenance overhead.
5

Balance Index Count

Don’t over-index - each index adds overhead for INSERT, UPDATE, and DELETE operations.
6

Use Covering Indexes

Create covering indexes to avoid table lookups for frequently accessed data.
7

Analyze Query Plans

Use EXPLAIN QUERY PLAN to understand how indexes are being used.
8

Regular Maintenance

Perform regular index maintenance including rebuilding and statistics updates.
Effective indexing is crucial for database performance. Always analyze your query patterns, monitor index usage, and maintain a balance between query performance and maintenance overhead.