Improving this MySQL Query - Select as sub-query

Question

I have this 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

This returns 900~ rows in around 0.7 seconds. This is OK-ish, but there are more lines like this required

(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,

For example

   (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 past_shot_id,

Adding this increases the load time to 10s of seconds which is far too long and the page often doesn't load at all or MySQL just locks up and using show processlist shows that the query is just sat there sending data.

Removing the ORDER BY s.shot_number ASC clause in those sub queries reduces the query time down to 0.05 seconds which is much much better. But the ORDER BY is required to ensure that the next or past row (shot) is returned, rather than any old random row.

How can I improve this query to make it run faster and return the same results. Perhaps my approach for obtaining the next and past rows is sub optimal and I need to look at a different way of returning those next and previous row IDs?

EDIT - additional background info

The query was fine on my testing domain, a subdomain. But when moved to the live domain the issues started. Hardly anything was changed yet the whole site came to halt because of these new slow queries. Key notes:

• Different domain
• Different folder in /var/www
• Same DB
• Same DB credentials
• Same code
• Added indexes in an attempt to fix - this didn't help

Could any of these affected the load time?

Answer 1

To expand on Strawberry's answer, doing additional left-join for a "pre-query" to get all the prior / next IDs, then join out to get whatever details you need.

select
      Shot.ID,
      Shot.Hole,
      Shot.Distance as Distance_Remaining,
      Shot.Type as Hit_Type,
      Shot.Area as Onto
      PriorShot.Hole as PriorHole,
      PriorShot.Distance as PriorDistanceRemain,
      NextShot.Hole as NextHole,
      NextShot.Distance as NextDistanceRemain
   from
      ( SELECT 
              shot.id, 
              MIN(nextshot.id) as NextShotID,
              MAX(priorshot.id) as PriorShotID
           FROM 
              round 
                 JOIN shot 
                    on round.id = shot.round_id
                    LEFT JOIN shot nextshot
                       ON shot.round_id = nextshot.round_id
                       AND shot.hole = nextshot.hole
                       AND shot.shot_number < nextshot.shot_number
                    LEFT JOIN shot priorshot
                       ON shot.round_id = priorshot.round_id
                       AND shot.hole = priorshot.hole
                       AND shot.shot_number > priorshot.shot_number
           WHERE
              round.uID = 78
           GROUP BY 
              shot.id ) AllShots
         JOIN Shot
            on AllShots.id = Shot.ID
            LEFT JOIN shot PriorShot
               on AllShots.PriorShotID = PriorShot.ID
            LEFT JOIN shot NextShot
               on AllShots.NextShotID = NextShot.ID

The inner query gets only those for round.uID = 78, then you can join to the next / prior as needed. I did not add the joins to the course and round tables as no result columns were presented, but could easily be added.

Answer 2

I wonder how well the following performs. It replaces the joining operations with string operations.

  SELECT shot.hole AS hole, shot.id AS id,
         substring_index(substring_index(shots, ',', find_in_set(shot.id, ss.shots) + 1), ',', -1
                        ) as nextsi,
         substring_index(substring_index(shots, ',', find_in_set(shot.id, ss.shots) - 1), ',', -1
                        ) as prevsi,
         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 join
       (select s.round_id, s.hole, group_concat(s.id order by s.shot_number) as shots
        from shot s
        group by s.round_id, s.hole
       ) ss
       on ss.round_id = shot.round_id and ss.hole = shot.hole
  WHERE round.uID = 78

Note that this doesn't work fully -- it will produce erroneous results on the first and last shot. I'm wondering how the performance is before fixing those details.

Answer 3

This will get marked down in a minute for 'not being an answer', but it illustrates a possible solution without simply handing it to you on a plate....

 SELECT * FROM ints;
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 | 8 |
 | 9 |
 +---+

 SELECT x.i, MIN(y.i) FROM ints x LEFT JOIN ints y ON y.i > x.i GROUP BY x.i;
 +---+----------+
 | i | MIN(y.i) |
 +---+----------+
 | 0 |        1 |
 | 1 |        2 |
 | 2 |        3 |
 | 3 |        4 |
 | 4 |        5 |
 | 5 |        6 |
 | 6 |        7 |
 | 7 |        8 |
 | 8 |        9 |
 | 9 |     NULL |
 +---+----------+