53 lines
2.7 KiB
MySQL
53 lines
2.7 KiB
MySQL
|
-- Database Performance Optimizations for SkyArtShop
|
||
|
|
-- Run these commands to add indexes and optimize queries
|
||
|
|
|
||
|
|
-- Products table indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_products_isactive ON products(isactive) WHERE isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_products_isfeatured ON products(isfeatured) WHERE isfeatured = true AND isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_products_slug ON products(slug) WHERE isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category) WHERE isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_products_createdat ON products(createdat DESC) WHERE isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_products_composite ON products(isactive, isfeatured, createdat DESC);
|
||
|
|
|
||
|
|
-- Product images indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_product_images_product_id ON product_images(product_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_product_images_is_primary ON product_images(product_id, is_primary) WHERE is_primary = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_product_images_display_order ON product_images(product_id, display_order, created_at);
|
||
|
|
|
||
|
|
-- Blog posts indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_blogposts_ispublished ON blogposts(ispublished) WHERE ispublished = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_blogposts_slug ON blogposts(slug) WHERE ispublished = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_blogposts_createdat ON blogposts(createdat DESC) WHERE ispublished = true;
|
||
|
|
|
||
|
|
-- Portfolio projects indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_portfolio_isactive ON portfolioprojects(isactive) WHERE isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_portfolio_display ON portfolioprojects(displayorder ASC, createdat DESC) WHERE isactive = true;
|
||
|
|
|
||
|
|
-- Pages indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages(slug) WHERE isactive = true;
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_pages_isactive ON pages(isactive) WHERE isactive = true;
|
||
|
|
|
||
|
|
-- Homepage sections indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_homepagesections_display ON homepagesections(displayorder ASC);
|
||
|
|
|
||
|
|
-- Team members indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_team_members_display ON team_members(display_order ASC, created_at DESC);
|
||
|
|
|
||
|
|
-- Session table optimization (if using pg-session)
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_session_expire ON session(expire);
|
||
|
|
|
||
|
|
-- Analyze tables to update statistics
|
||
|
|
ANALYZE products;
|
||
|
|
ANALYZE product_images;
|
||
|
|
ANALYZE blogposts;
|
||
|
|
ANALYZE portfolioprojects;
|
||
|
|
ANALYZE pages;
|
||
|
|
ANALYZE homepagesections;
|
||
|
|
ANALYZE team_members;
|
||
|
|
|
||
|
|
-- Add comments for documentation
|
||
|
|
COMMENT ON INDEX idx_products_isactive IS 'Optimizes filtering active products';
|
||
|
|
COMMENT ON INDEX idx_products_isfeatured IS 'Optimizes featured products query';
|
||
|
|
COMMENT ON INDEX idx_products_slug IS 'Optimizes product lookup by slug';
|
||
|
|
COMMENT ON INDEX idx_products_composite IS 'Composite index for common query patterns';
|