select * from `order_photos` where exists (select * from `orders` where `order_photos`.`order_id` = `orders`.`id` and `is_completed` = '1') and (exists (select * from `athletes` where `order_photos`.`athlete_id` = `athletes`.`id` and (exists (select * from `photos` inner join `athlete_photos` on `photos`.`id` = `athlete_photos`.`photo_id` where `athletes`.`id` = `athlete_photos`.`athlete_id` and `partner_id` = '1' and exists (select * from `albums` where `photos`.`album_id` = `albums`.`id` and `competition_id` = order_photos.competition_id) and `is_published` = '1'))) or exists (select * from `photos` where `order_photos`.`id` = `photos`.`order_photo_id` and `partner_id` = '1' and exists (select * from `albums` where `photos`.`album_id` = `albums`.`id` and `competition_id` = order_photos.competition_id)))

SELECT *
FROM `order_photos`
WHERE EXISTS ( SELECT * FROM `orders` WHERE `order_photos`.`order_id` = `orders`.`id` AND `is_completed` = '1')  AND ( EXISTS ( SELECT * FROM `athletes` WHERE `order_photos`.`athlete_id` = `athletes`.`id` AND ( EXISTS ( SELECT * FROM `photos` INNER JOIN `athlete_photos` ON `photos`.`id` = `athlete_photos`.`photo_id` WHERE `athletes`.`id` = `athlete_photos`.`athlete_id` AND `partner_id` = '1' AND EXISTS ( SELECT * FROM `albums` WHERE `photos`.`album_id` = `albums`.`id` AND `competition_id` = order_photos.competition_id) AND `is_published` = '1')))  OR EXISTS ( SELECT * FROM `photos` WHERE `order_photos`.`id` = `photos`.`order_photo_id` AND `partner_id` = '1' AND EXISTS ( SELECT * FROM `albums` WHERE `photos`.`album_id` = `albums`.`id` AND `competition_id` = order_photos.competition_id)))

Edit: here's the explains:

 - EXPLAIN #1: `order_photos` (PRIMARY)
Params
id 1
select_type PRIMARY
table order_photos
partitions null
type ALL
possible_keys null
key null
key_len null
ref null
rows 24
filtered 100
Extra Using where
 - EXPLAIN #6: `photos` (DEPENDENT SUBQUERY)
Params
id 6
select_type DEPENDENT SUBQUERY
table photos
partitions null
type ref
possible_keys photos_partner_id_index,photos_order_photo_id_index
key photos_order_photo_id_index
key_len 5
ref llspark.order_photos.id
rows 31
filtered 100
Extra Using index condition; Using where
 - EXPLAIN #7: `albums` (DEPENDENT SUBQUERY)
Params
id 7
select_type DEPENDENT SUBQUERY
table albums
partitions null
type eq_ref
possible_keys PRIMARY,albums_competition_id_index
key PRIMARY
key_len 4
ref llspark.photos.album_id
rows 1
filtered 10
Extra Using where
 - EXPLAIN #3: `athletes` (DEPENDENT SUBQUERY)
Params
id 3
select_type DEPENDENT SUBQUERY
table athletes
partitions null
type eq_ref
possible_keys PRIMARY
key PRIMARY
key_len 4
ref llspark.order_photos.athlete_id
rows 1
filtered 100
Extra Using where; Using index
 - EXPLAIN #4: `athlete_photos` (DEPENDENT SUBQUERY)
Params
id 4
select_type DEPENDENT SUBQUERY
table athlete_photos
partitions null
type ALL
possible_keys athlete_photos_athlete_id_index,athlete_photos_photo_id_index
key null
key_len null
ref null
rows 7
filtered 14.285715103149414
Extra Using where
 - EXPLAIN #4: `photos` (DEPENDENT SUBQUERY)
Params
id 4
select_type DEPENDENT SUBQUERY
table photos
partitions null
type eq_ref
possible_keys PRIMARY,photos_partner_id_index
key PRIMARY
key_len 4
ref llspark.athlete_photos.photo_id
rows 1
filtered 10
Extra Using where
 - EXPLAIN #5: `albums` (DEPENDENT SUBQUERY)
Params
id 5
select_type DEPENDENT SUBQUERY
table albums
partitions null
type eq_ref
possible_keys PRIMARY,albums_competition_id_index
key PRIMARY
key_len 4
ref llspark.photos.album_id
rows 1
filtered 10
Extra Using where
 - EXPLAIN #2: `orders` (DEPENDENT SUBQUERY)
Params
id 2
select_type DEPENDENT SUBQUERY
table orders
partitions null
type eq_ref
possible_keys PRIMARY
key PRIMARY
key_len 4
ref llspark.order_photos.order_id
rows 1
filtered 10
Extra Using where