database/phase2-optimize-db.sql

USE care_people;

ALTER TABLE doctors
ADD INDEX IF NOT EXISTS idx_doctor_specialty_status (department, is_active),
ADD INDEX IF NOT EXISTS idx_doctor_name_search (name);

ALTER TABLE patients
ADD INDEX IF NOT EXISTS idx_patient_phone_name (phone_number, name),
ADD INDEX IF NOT EXISTS idx_patient_name_search (name),
ADD INDEX IF NOT EXISTS idx_patient_gender_dob (gender, date_of_birth);

ALTER TABLE appointments
ADD INDEX IF NOT EXISTS idx_appointment_doctor_patient_date (doctor_id, patient_phone, appointment_date),
ADD INDEX IF NOT EXISTS idx_appointment_doctor_status (doctor_id, status),
ADD INDEX IF NOT EXISTS idx_appointment_patient_status (patient_phone, status),
ADD INDEX IF NOT EXISTS idx_appointment_date_status (appointment_date, status);

ALTER TABLE prescriptions
ADD INDEX IF NOT EXISTS idx_prescription_doctor_patient (doctor_id, patient_phone),
ADD INDEX IF NOT EXISTS idx_prescription_issued_date (issued_at DESC);

ALTER TABLE audit_logs
ADD INDEX IF NOT EXISTS idx_audit_logs_action_date (action, created_at DESC),
ADD INDEX IF NOT EXISTS idx_audit_logs_entity_lookup (entity_type, entity_id);