The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `course` ADD INDEX `course_idx_id` (`id`);
ALTER TABLE `round` ADD INDEX `round_idx_uid` (`uID`);
ALTER TABLE `shot` ADD INDEX `shot_idx_round_id` (`round_id`);
ALTER TABLE `shot` ADD INDEX `shot_idx_hole_round_id_shot_numbe` (`hole`,`round_id`,`shot_number`);
ALTER TABLE `shot` ADD INDEX `shot_idx_shot_number` (`shot_number`);

The optimized query:

SELECT
        shot.hole AS hole,
        shot.id AS id,
        (SELECT
            s.id 
        FROM
            shot AS s 
        WHERE
            s.hole = shot.hole 
            AND s.shot_number > shot.shot_number 
            AND shot.round_id = s.round_id 
        ORDER BY
            s.shot_number ASC LIMIT 1) AS next_shot_id,
        shot.distance AS distance_remaining,
        shot.type AS hit_type,
        shot.area AS onto 
    FROM
        shot 
    JOIN
        course 
            ON shot.course_id = course.id 
    JOIN
        round 
            ON shot.round_id = round.id 
    WHERE
        round.uID = 78