`scripts/optimize-database.ts`

125 lines
#!/usr/bin/env ts-node
// Database Optimization Script
// Creates indexes for faster queries

import Database from 'better-sqlite3'
import path from 'path'

const DB_PATH = path.join(process.cwd(), 'data', 'handbags.db')

console.log('🔧 Optimizing database...')
console.log(`Database: ${DB_PATH}\n`)

const db = new Database(DB_PATH)

try {
  // Enable Write-Ahead Logging for better performance
  console.log('⚙️  Enabling WAL mode...')
  db.pragma('journal_mode = WAL')

  // Create indexes for frequently queried columns
  console.log('📊 Creating indexes...')

  // Index on brand (most common filter)
  console.log('  - Creating index on brand...')
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_listings_brand
    ON listings(brand)
  `)

  // Index on price_usd for sorting and filtering
  console.log('  - Creating index on price_usd...')
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_listings_price
    ON listings(price_usd)
  `)

  // Index on is_active for filtering active listings
  console.log('  - Creating index on is_active...')
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_listings_active
    ON listings(is_active)
  `)

  // Composite index for common query pattern (active + price range + brand)
  console.log('  - Creating composite index...')
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_listings_active_price_brand
    ON listings(is_active, price_usd, brand)
  `)

  // Index on crawled_at for finding latest data
  console.log('  - Creating index on crawled_at...')
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_listings_crawled
    ON listings(crawled_at DESC)
  `)

  // Optimize database file
  console.log('\n🗜️  Running VACUUM to optimize database file...')
  db.exec('VACUUM')

  // Analyze tables for query planner
  console.log('📈 Running ANALYZE to update statistics...')
  db.exec('ANALYZE')

  // Get database stats
  const stats = db.prepare(`
    SELECT
      COUNT(*) as total_listings,
      COUNT(DISTINCT brand) as total_brands,
      (SELECT name FROM sqlite_master WHERE type='index') as sample_index
    FROM listings
  `).get() as any

  console.log('\n✅ Optimization complete!')
  console.log(`   Total listings: ${stats.total_listings.toLocaleString()}`)
  console.log(`   Total brands: ${stats.total_brands}`)

  // Show all indexes
  const indexes = db.prepare(`
    SELECT name, sql
    FROM sqlite_master
    WHERE type = 'index'
      AND tbl_name = 'listings'
      AND name NOT LIKE 'sqlite_%'
  `).all()

  console.log('\n📑 Active indexes:')
  indexes.forEach((idx: any) => {
    console.log(`   - ${idx.name}`)
  })

  // Test query performance
  console.log('\n⚡ Testing query performance...')

  const testStart = Date.now()
  db.prepare(`
    SELECT COUNT(*)
    FROM listings
    WHERE is_active = 1
      AND price_usd > 1000
      AND price_usd < 100000
      AND brand = 'Hermes'
  `).get()
  const testDuration = Date.now() - testStart

  console.log(`   Query time: ${testDuration}ms`)

  if (testDuration < 10) {
    console.log('   ✅ Excellent performance!')
  } else if (testDuration < 50) {
    console.log('   ✓ Good performance')
  } else {
    console.log('   ⚠️  Could be faster')
  }

} catch (error) {
  console.error('❌ Error optimizing database:', error)
  process.exit(1)
} finally {
  db.close()
}

console.log('\n✨ Database optimization complete!\n')