Explain the trade-offs between different database indexing strategies in MySQL for compensation data queries at Pave.
Use the supplied schema and data to demonstrate the compensation query workload discussed in your answer. Explain which indexes you would consider, how column order affects composite indexes, and the costs of writes, storage, and low-selectivity columns.
employee_name, effective_date, base_salary, and currencyeffective_date descending and employee_name ascending| Column | Type | Description |
|---|---|---|
| employee_idPK | INTEGER | Unique employee identifier |
| employee_name | VARCHAR(100) | Employee display name |
| department | VARCHAR(80) | Employee department |
| Column | Type | Description |
|---|---|---|
| compensation_idPK | INTEGER | Unique compensation record identifier |
| employee_id | INTEGER | Referenced employee |
| effective_date | DATE | Date the compensation takes effect |
| status | VARCHAR(20) | Approval status of the compensation record |
| base_salary | NUMERIC(14,2) | Annual base salary amount |
| currency | VARCHAR(3) | Salary currency code |
| notes | VARCHAR(255) | Optional compensation notes |