# Homepage Section Projects Table Optimization Documentation

## Overview
This document describes all indexes, constraints, triggers, and optimization strategies for the `homepage_section_projects` junction table.

## Indexes

### 1. Primary Index
- **Name**: `PRIMARY` (auto-generated)
- **Columns**: `id`
- **Purpose**: Primary key for the table
- **Usage**: All queries that reference records by ID

### 2. Foreign Key Indexes
- **Name**: `homepage_section_projects_homepage_section_id_foreign` (auto-generated)
- **Columns**: `homepage_section_id`
- **Purpose**: Foreign key to `homepage_sections.id`
- **Usage**: 
  - Joins between `homepage_sections` and `homepage_section_projects`
  - Filtering by section
  - Cascade delete operations

- **Name**: `homepage_section_projects_project_id_foreign` (auto-generated)
- **Columns**: `project_id`
- **Purpose**: Foreign key to `projects.id`
- **Usage**:
  - Joins between `projects` and `homepage_section_projects`
  - Filtering by project
  - Restrict delete operations

### 3. Composite Index: Section + Field Key (Unique)
- **Name**: `hsp_section_field_unique`
- **Columns**: `['homepage_section_id', 'field_key']`
- **Type**: UNIQUE
- **Purpose**: Prevents duplicate field assignments within a section
- **Usage**:
  - Enforcing uniqueness constraint
  - Queries filtering by section and field_key
  - Example: `WHERE homepage_section_id = ? AND field_key = 'hero'`

### 4. Composite Index: Section + Display Order
- **Name**: `hsp_section_order_idx`
- **Columns**: `['homepage_section_id', 'display_order']`
- **Purpose**: Optimizes ordering queries within a section
- **Usage**:
  - `ORDER BY display_order` queries filtered by section
  - Example: `WHERE homepage_section_id = ? ORDER BY display_order ASC`
  - Used by `HomepageSection::sectionProjects()` relationship

### 5. Composite Index: Section + Project (NEW)
- **Name**: `hsp_section_project_idx`
- **Columns**: `['homepage_section_id', 'project_id']`
- **Purpose**: Optimizes join queries between sections and projects
- **Usage**:
  - Eager loading with `with(['sectionProjects.project'])`
  - Queries checking if a project exists in a section
  - Reverse lookups (find sections containing a project)
  - Example: `WHERE homepage_section_id = ? AND project_id = ?`

### 6. Index: Display Order (NEW)
- **Name**: `hsp_display_order_idx`
- **Columns**: `display_order`
- **Purpose**: Optimizes sorting operations across all sections
- **Usage**:
  - Global ordering queries
  - Statistics queries (e.g., count by display_order)
  - Example: `ORDER BY display_order ASC`

### 7. Index: Field Key (NEW)
- **Name**: `hsp_field_key_idx`
- **Columns**: `field_key`
- **Purpose**: Optimizes filtered queries by field_key
- **Usage**:
  - Filtering by field_key pattern (hero, weekly_*, story_*)
  - Queries like `WHERE field_key LIKE 'weekly_%'`
  - Example: `WHERE field_key = 'hero'` or `WHERE field_key LIKE 'story_%'`

## Check Constraints

### 1. Display Order Non-Negative
- **Name**: `chk_display_order_non_negative`
- **Constraint**: `display_order >= 0`
- **Purpose**: Ensures display_order is always non-negative
- **Validation**: Prevents negative values that could cause sorting issues

### 2. Field Key Format Validation
- **Name**: `chk_field_key_format`
- **Constraint**: 
  ```sql
  field_key IS NULL OR
  field_key = 'hero' OR
  field_key REGEXP '^weekly_[1-6]$' OR
  field_key REGEXP '^story_[1-4]$'
  ```
- **Purpose**: Validates field_key format at database level
- **Allowed Values**:
  - `NULL`
  - `'hero'`
  - `'weekly_1'` through `'weekly_6'`
  - `'story_1'` through `'story_4'`

## Triggers

### 1. Project Count Validation (Before Insert)
- **Name**: `trg_validate_project_count_before_insert`
- **Event**: BEFORE INSERT
- **Purpose**: Validates project count limits before inserting new records
- **Rules**:
  - Hero section: Maximum 1 project
  - Weekly project section: Maximum 6 projects
  - Project story section: Maximum 4 projects
- **Error**: Raises SQLSTATE '45000' with descriptive message if limit exceeded

### 2. Project Count Validation (Before Update)
- **Name**: `trg_validate_project_count_before_update`
- **Event**: BEFORE UPDATE
- **Purpose**: Validates project count limits when updating records
- **Rules**: Same as insert trigger
- **Note**: Excludes current row from count to allow updates within limits

### 3. Duplicate Project Prevention (Before Insert)
- **Name**: `trg_prevent_duplicate_project_before_insert`
- **Event**: BEFORE INSERT
- **Purpose**: Prevents the same project from being assigned multiple times to the same section
- **Note**: Additional validation beyond unique constraint on `['homepage_section_id', 'field_key']`

### 4. Duplicate Project Prevention (Before Update)
- **Name**: `trg_prevent_duplicate_project_before_update`
- **Event**: BEFORE UPDATE
- **Purpose**: Prevents duplicate projects when updating records
- **Note**: Excludes current row from duplicate check

## Query Optimization Strategies

### 1. Eager Loading
Always use eager loading to prevent N+1 queries:

```php
// Good: Eager loads relationships
HomepageSection::with(['sectionProjects.project'])->get();

// Bad: Causes N+1 queries
HomepageSection::get();
foreach ($sections as $section) {
    $section->sectionProjects; // N+1 query
}
```

### 2. Query Caching
Use Laravel's cache for frequently accessed sections:

```php
Cache::remember('homepage_sections_active', 300, function () {
    return HomepageSection::where('is_active', true)
        ->with(['sectionProjects.project'])
        ->get();
});
```

### 3. Index Usage
Ensure queries use appropriate indexes:

```php
// Uses hsp_section_order_idx
HomepageSectionProject::where('homepage_section_id', $id)
    ->orderBy('display_order')
    ->get();

// Uses hsp_field_key_idx
HomepageSectionProject::where('field_key', 'hero')->get();

// Uses hsp_section_project_idx
HomepageSectionProject::where('homepage_section_id', $sectionId)
    ->where('project_id', $projectId)
    ->first();
```

## Performance Monitoring Queries

### 1. Index Usage Statistics
```sql
-- Check index usage for homepage_section_projects table
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY,
    INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'homepage_section_projects'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
```

### 2. Table Size and Row Count
```sql
-- Get table size and row count
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size (MB)',
    ROUND((DATA_LENGTH / 1024 / 1024), 2) AS 'Data (MB)',
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) AS 'Index (MB)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'homepage_section_projects';
```

### 3. Slow Query Analysis
```sql
-- Find slow queries (requires slow query log enabled)
-- Check MySQL slow query log or use EXPLAIN on queries

-- Example: Analyze query performance
EXPLAIN SELECT 
    hsp.*, 
    p.title, 
    p.image 
FROM homepage_section_projects hsp
JOIN projects p ON hsp.project_id = p.id
WHERE hsp.homepage_section_id = 1
ORDER BY hsp.display_order ASC;
```

### 4. Index Cardinality Check
```sql
-- Check index cardinality (uniqueness)
SELECT 
    INDEX_NAME,
    CARDINALITY,
    (SELECT COUNT(*) FROM homepage_section_projects) AS total_rows,
    ROUND((CARDINALITY / (SELECT COUNT(*) FROM homepage_section_projects)) * 100, 2) AS selectivity_percent
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'homepage_section_projects'
  AND INDEX_NAME != 'PRIMARY'
GROUP BY INDEX_NAME, CARDINALITY;
```

### 5. Missing Indexes Detection
```sql
-- Check for queries that might benefit from additional indexes
-- Use MySQL's performance_schema or slow query log analysis
-- Look for queries with:
-- - Full table scans (type = ALL)
-- - Filesort operations
-- - High rows examined vs rows returned ratio
```

### 6. Trigger Performance
```sql
-- Check trigger execution time (if performance_schema is enabled)
SELECT 
    OBJECT_NAME,
    OBJECT_TYPE,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000000 AS total_time_seconds,
    AVG_TIMER_WAIT / 1000000000000 AS avg_time_seconds
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_NAME LIKE 'trg_%'
ORDER BY SUM_TIMER_WAIT DESC;
```

### 7. Constraint Violations Monitoring
```sql
-- Monitor constraint violations (requires error logging)
-- Check application logs for:
-- - Check constraint violations
-- - Trigger errors (SQLSTATE '45000')
-- - Foreign key constraint violations
```

## Recommended Query Patterns

### Pattern 1: Get Section with Projects (Optimized)
```php
// Uses: hsp_section_order_idx, hsp_section_project_idx
$section = HomepageSection::with([
    'sectionProjects' => function ($query) {
        $query->orderBy('display_order');
    },
    'sectionProjects.project' => function ($query) {
        $query->where('status', 'active');
    }
])->find($id);
```

### Pattern 2: Find Projects by Section Key
```php
// Uses: hsp_section_field_unique, hsp_field_key_idx
$projects = HomepageSectionProject::whereHas('homepageSection', function ($q) {
    $q->where('section_key', 'hero');
})
->where('field_key', 'hero')
->with('project')
->get();
```

### Pattern 3: Check if Project Exists in Section
```php
// Uses: hsp_section_project_idx
$exists = HomepageSectionProject::where('homepage_section_id', $sectionId)
    ->where('project_id', $projectId)
    ->exists();
```

## Maintenance Queries

### Rebuild Indexes
```sql
-- Rebuild all indexes (if needed after bulk operations)
ALTER TABLE homepage_section_projects ENGINE=InnoDB;
ANALYZE TABLE homepage_section_projects;
```

### Update Statistics
```sql
-- Update table statistics for query optimizer
ANALYZE TABLE homepage_section_projects;
```

### Check Table Health
```sql
-- Check for table corruption or issues
CHECK TABLE homepage_section_projects;
```

## Index Maintenance Schedule

- **Weekly**: Run `ANALYZE TABLE` to update statistics
- **Monthly**: Review index usage statistics
- **Quarterly**: Review and optimize slow queries
- **As Needed**: Rebuild indexes after bulk data operations

## Notes

- All indexes use InnoDB storage engine (default in Laravel)
- Index names are prefixed with `hsp_` (homepage_section_projects) for easy identification
- Triggers use `SIGNAL SQLSTATE '45000'` for user-defined errors
- Check constraints require MySQL 8.0.16 or later
- Triggers may have slight performance impact on INSERT/UPDATE operations
- Consider monitoring trigger execution time in production