Examine the appointments table definition which contains one million rows: Now, examine this statement which executes successfully: Which statement will improve query performance?

Question

Exhibits

Options

• AALTER TABLE appointments add index IX_4<attendant_id, payment, credit)
• BALTER TABLE appointments add index IX_1(credit,payment)
• CALTER TABLE appointments add index IX_2(attendant_session_id, created_by)
• DALTER TABLE appointments add index IX_3(attendant_id, created_by)

• A
  65% (15)
• B
  17% (4)
• C
  13% (3)
• D
  4% (1)

Explanation

Option A creates a composite index on (attendant_id, payment, credit) that directly matches the query's filter and projected columns - MySQL can resolve the entire query from the index alone (a covering index), avoiding expensive full-table row lookups across one million records. Option B (credit, payment) omits attendant_id, which is almost certainly the primary filter column; without it as the leftmost key, the index cannot efficiently narrow the result set. Option C (attendant_session_id, created_by) indexes columns not referenced in the query's WHERE or SELECT clause, so the optimizer simply won't use it. Option D (attendant_id, created_by) starts correctly with attendant_id but swaps in created_by instead of payment and credit, meaning the query still must fetch full rows to retrieve those missing values.

Memory tip: Think of a composite index like a phone book - it can only be sorted by the columns listed left-to-right. The winning index matches the query's filter column first, then adds every other column the query touches to make it a covering index and eliminate table row lookups entirely.

No community discussion yet for this question.