63 lines
2.8 KiB
SQL
63 lines
2.8 KiB
SQL
-- Performance Optimization Indexes
|
|
-- Run this migration to add indexes for improved query performance
|
|
-- Created: 2025-12-20
|
|
|
|
-- ===================================
|
|
-- MATERIAL PRICES - Additional Indexes
|
|
-- ===================================
|
|
-- Index for price lookups by date (if valid_from exists)
|
|
-- CREATE INDEX IF NOT EXISTS idx_material_prices_valid_from ON material_prices(valid_from);
|
|
|
|
-- Index for active material filtering
|
|
CREATE INDEX IF NOT EXISTS idx_material_prices_active ON material_prices(category, price);
|
|
|
|
-- Composite index for common search pattern
|
|
CREATE INDEX IF NOT EXISTS idx_material_prices_search ON material_prices(name, category, supplier_name);
|
|
|
|
-- ===================================
|
|
-- LABOR PRICES - Additional Indexes
|
|
-- ===================================
|
|
-- Index for price lookups
|
|
CREATE INDEX IF NOT EXISTS idx_labor_prices_type_rate ON labor_prices(project_type, labor_rate);
|
|
|
|
-- Composite index for common queries
|
|
CREATE INDEX IF NOT EXISTS idx_labor_prices_search ON labor_prices(project_type, difficulty_level);
|
|
|
|
-- ===================================
|
|
-- CUSTOMER PROJECTS - Performance Indexes
|
|
-- ===================================
|
|
-- These should be added if not already present
|
|
CREATE INDEX IF NOT EXISTS idx_customer_projects_status ON customer_projects(status);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_projects_customer ON customer_projects(customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_projects_created ON customer_projects(created_at);
|
|
|
|
-- ===================================
|
|
-- PROJECT MATERIALS - Performance Indexes
|
|
-- ===================================
|
|
CREATE INDEX IF NOT EXISTS idx_project_materials_project ON project_materials(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_materials_varenr ON project_materials(varenr);
|
|
|
|
-- ===================================
|
|
-- SMART PACKAGES - Performance Indexes
|
|
-- ===================================
|
|
CREATE INDEX IF NOT EXISTS idx_smart_packages_category ON smart_packages(category_id);
|
|
CREATE INDEX IF NOT EXISTS idx_smart_packages_active ON smart_packages(is_active);
|
|
|
|
-- ===================================
|
|
-- QUOTES - Performance Indexes
|
|
-- ===================================
|
|
CREATE INDEX IF NOT EXISTS idx_quotes_project ON quotes(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_quotes_status ON quotes(status);
|
|
CREATE INDEX IF NOT EXISTS idx_quotes_created ON quotes(created_at);
|
|
|
|
-- ===================================
|
|
-- BYGMA PRICES - Performance Indexes
|
|
-- ===================================
|
|
CREATE INDEX IF NOT EXISTS idx_bygma_prices_varenr ON bygma_prices(varenr);
|
|
CREATE INDEX IF NOT EXISTS idx_bygma_prices_search ON bygma_prices(name, category);
|
|
|
|
-- ===================================
|
|
-- VERIFY INDEXES
|
|
-- ===================================
|
|
SELECT 'Performance indexes migration complete' as status;
|