`auction-viewer/db-optimization.sql`

170 lines
-- ============================================
-- Database Optimization Script
-- LUXVAULT Auction Viewer
-- ============================================

-- Enable Write-Ahead Logging for better concurrency
PRAGMA journal_mode = WAL;

-- Set synchronous mode to NORMAL for better performance
PRAGMA synchronous = NORMAL;

-- Increase cache size (64MB)
PRAGMA cache_size = -64000;

-- Use memory for temporary storage
PRAGMA temp_store = MEMORY;

-- Enable memory-mapped I/O (30GB)
PRAGMA mmap_size = 30000000000;

-- ============================================
-- Create Performance Indexes
-- ============================================

-- Index on search_term (brand) - frequently filtered
CREATE INDEX IF NOT EXISTS idx_auctions_search_term
ON auctions(search_term);

-- Index on created_at - for chronological sorting
CREATE INDEX IF NOT EXISTS idx_auctions_created_at
ON auctions(created_at DESC);

-- Index on current_price - for price range queries
CREATE INDEX IF NOT EXISTS idx_auctions_current_price
ON auctions(current_price);

-- Index on auction_house - for filtering
CREATE INDEX IF NOT EXISTS idx_auctions_auction_house
ON auctions(auction_house);

-- Index on end_date - for time-based queries
CREATE INDEX IF NOT EXISTS idx_auctions_end_date
ON auctions(end_date);

-- Index on data_hash - for deduplication
CREATE INDEX IF NOT EXISTS idx_auctions_data_hash
ON auctions(data_hash);

-- Composite index for common query patterns
CREATE INDEX IF NOT EXISTS idx_auctions_brand_price
ON auctions(search_term, current_price, created_at DESC);

-- Composite index for deal hunting
CREATE INDEX IF NOT EXISTS idx_auctions_deals
ON auctions(estimate_low, current_price)
WHERE current_price > 0 AND estimate_low > 0;

-- Index for auction ending soon queries
CREATE INDEX IF NOT EXISTS idx_auctions_ending_soon
ON auctions(end_date, current_price)
WHERE end_date IS NOT NULL;

-- ============================================
-- Analyze Tables for Query Optimizer
-- ============================================

ANALYZE auctions;
ANALYZE auctions_fts;

-- ============================================
-- Archive Old Data (optional)
-- ============================================

-- Create archive table if it doesn't exist
CREATE TABLE IF NOT EXISTS auctions_archive (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    auction_id TEXT UNIQUE,
    title TEXT,
    auction_house TEXT,
    current_price REAL,
    estimate_low REAL,
    estimate_high REAL,
    end_date TEXT,
    lot_number TEXT,
    url TEXT,
    search_term TEXT,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index on archive table
CREATE INDEX IF NOT EXISTS idx_archive_created_at
ON auctions_archive(created_at);

CREATE INDEX IF NOT EXISTS idx_archive_archived_at
ON auctions_archive(archived_at);

-- ============================================
-- Vacuum and Optimize
-- ============================================

-- Rebuild database to reclaim space and optimize
-- VACUUM;

-- Note: Uncomment VACUUM command and run manually during low-traffic periods

-- ============================================
-- Statistics
-- ============================================

-- Check index usage
SELECT
    name,
    tbl_name,
    CASE
        WHEN sql LIKE '%UNIQUE%' THEN 'UNIQUE'
        ELSE 'INDEX'
    END as type
FROM sqlite_master
WHERE type = 'index'
AND tbl_name = 'auctions'
ORDER BY name;

-- Check table size
SELECT
    name,
    (pgsize * pgcount) / 1024.0 / 1024.0 as size_mb
FROM dbstat
WHERE name = 'auctions';

-- ============================================
-- Query Performance Testing
-- ============================================

-- Test query plan for brand filtering
EXPLAIN QUERY PLAN
SELECT * FROM auctions
WHERE search_term = 'Hermes Birkin'
ORDER BY created_at DESC
LIMIT 50;

-- Test query plan for price range
EXPLAIN QUERY PLAN
SELECT * FROM auctions
WHERE current_price BETWEEN 5000 AND 20000
ORDER BY current_price ASC
LIMIT 50;

-- Test query plan for full-text search
EXPLAIN QUERY PLAN
SELECT a.* FROM auctions a
WHERE a.id IN (
    SELECT rowid FROM auctions_fts
    WHERE auctions_fts MATCH 'birkin leather'
)
LIMIT 50;

-- Test query plan for best deals
EXPLAIN QUERY PLAN
SELECT *,
    CASE
        WHEN estimate_low > 0 AND current_price > 0
        THEN ((estimate_low - current_price) / estimate_low * 100)
        ELSE 0
    END as savings_percent
FROM auctions
WHERE current_price > 0 AND estimate_low > 0
ORDER BY savings_percent DESC
LIMIT 50;