indexes.sql

-- Drop existing indexes first (allows for clean re-runs)
DROP INDEX IF EXISTS users_email_idx;

DROP INDEX IF EXISTS events_date_idx;

DROP INDEX IF EXISTS tickets_user_id_idx;

DROP INDEX IF EXISTS tickets_event_id_idx;

DROP INDEX IF EXISTS tickets_purchased_at_idx;

DROP INDEX IF EXISTS concession_transactions_user_id_idx;

DROP INDEX IF EXISTS concession_transactions_event_id_idx;

DROP INDEX IF EXISTS concession_transactions_transaction_time_idx;

DROP INDEX IF EXISTS gate_entries_user_id_idx;

DROP INDEX IF EXISTS gate_entries_event_id_idx;

DROP INDEX IF EXISTS gate_entries_entry_time_idx;

-- Users table indexes
-- Email lookups for user authentication
CREATE UNIQUE INDEX users_email_idx ON users (email);

-- Events table indexes
-- Optimize date-based event searches and range queries
CREATE INDEX events_date_idx ON events (event_date);

-- Tickets table indexes
-- Optimize joins with users and events tables
-- Speed up time-based sales analysis
CREATE INDEX tickets_user_id_idx ON tickets (user_id);

CREATE INDEX tickets_event_id_idx ON tickets (event_id);

CREATE INDEX tickets_purchased_at_idx ON tickets (purchased_at);

-- Concession transactions table indexes
-- Optimize joins and time-based sales analysis
CREATE INDEX concession_transactions_user_id_idx ON concession_transactions (user_id);

CREATE INDEX concession_transactions_event_id_idx ON concession_transactions (event_id);

CREATE INDEX concession_transactions_transaction_time_idx ON concession_transactions (transaction_time);

-- Gate entries table indexes
-- Optimize entry analysis and crowd flow queries
CREATE INDEX gate_entries_user_id_idx ON gate_entries (user_id);

CREATE INDEX gate_entries_event_id_idx ON gate_entries (event_id);

CREATE INDEX gate_entries_entry_time_idx ON gate_entries (entry_time);

-- Note: These indexes support our common query patterns:
-- 1. Looking up users by email
-- 2. Finding events by date
-- 3. Analyzing ticket sales over time
-- 4. Tracking concession sales patterns
-- 5. Monitoring gate entry patterns
-- 6. All JOIN operations between tables