Mysql – How to copy the MySQL EXPLAIN result into a table

• First, please remove the parentheses from DISTINCT(reservations.id).
  DISTINCT is not a function.

• About performance, why do you join reservation_nights? You only seem to use that table in the ORDER BY. Assuming that a reservation can span several nights, you probably mean to use the last date (from the many that a reservation has) for the ordering and nothing else. This however, requires a join on reservation_nights and thus many rows for each reservation are created in the result set. So, you had to use DISTINCT to get rid of the duplicate rows. DISTINCT is often inefficient, especially when performed for big results sets and not in small/indexed tables.

Try this rewriting, that gets rid of the DISTINCT and pushes the ORDER BY / LIMIT 100 in a derived table where only base table is used. This technique which I call "LIMIT first, JOIN later" can be more efficient as the order by is done on less data (single table vs a large join) and then the joins are done on less data as well (max 100 rows from each of the joined tables):

SELECT 
    r.id, 
    r.referenceId, r.referenceStatus, r.status, 
    r.firstname, r.lastname, 
    r.dateArrival, r.daysToCheckIn, 
    r.rateCode, r.roomCode, 
    r.totalNights, r.rateRevenue, 
    r.dailyRate, r.totalRevenue, 
    r.foodBeverageRevenue, r.additionalRevenue, 
    c.name AS channelName, 
    cg.name AS channelGroupName, 
    m.name AS marketName, 
    p.totalReservations 
FROM 
    ( SELECT reservationId, MAX(dateStayed) AS lastDateStayed
      FROM reservation_nights
      GROUP BY reservationId
      ORDER BY MAX(dateStayed) DESC 
      LIMIT 100 
    ) AS rn
  INNER JOIN reservations AS r ON r.id = rn.reservationId 
  INNER JOIN channels AS c ON c.id = r.channelId 
  INNER JOIN channelgroups AS cg ON cg.id = c.channelgroupId 
  INNER JOIN markets AS m ON m.id = r.marketId 
  LEFT JOIN profiles AS p ON p.id = r.profileId 
ORDER BY 
    rn.lastDateStayed DESC ;

• If you don't already have one, add an index on reservation_nights (reservationId, dateStayed) to improve the efficiency of the subquery.

For some queries EXPLAIN tries to run some subqueries to get the stats - it is a known bug which is fixed in 5.6 and in MariaDB (10 at least, not sure about 5.5).

Your indexes are not good enough - you have two single-column indexes on yearly_project_commits - but the query can use only one at a time.

You should create one multicolumn index with both the columns (project_id, committer_id) - that should turn the subquery into index-only scan with direct access in join and make it substantially faster.