I'm using Laravel and I have one monster query that returns exactly what I need, and runs perfectly on my dev machine, but on production runs extremely slow and I get timeout errors.
Here's the query:
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
)
)
)
Here's my laravel code that generates this query:
$preorders = OrderPhoto::whereHas('order', function ($order) {
$order->completed();
})
->where(function ($query) use ($partner) {
$query->whereHas('athlete', function ($athlete) use ($partner) {
$athlete->where(function ($query) use ($partner) {
$query->whereHas('photos', function ($photo) use ($partner) {
$photo->where('partner_id', $partner->id);
$photo->whereHas('album', function ($album) {
$album->where('competition_id', DB::raw('order_photos.competition_id'));
});
});
});
});
$query->orWhereHas('photos', function ($photo) use ($partner) {
$photo->where('partner_id', $partner->id);
$photo->whereHas('album', function ($album) {
$album->where('competition_id', DB::raw('order_photos.competition_id'));
});
});
})
->with('competition')
->get();
Question is, should I add indexes to speed this up, or should I take a different approach? My photos table is the only one with a ton of records, over a million. I'm guessing that those sub-queries are expensive. Again, the output is exactly what I need and perfectly represents the relationships that must be this exact search.
The only way I can think of to take a different approach is to break this up into multiple queries and then combine/filter the results in php, which would be a bummer because this one query returns exactly what I need in the form I need it in without any further processing.
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
EXPLAIN SELECTfor that SQL query? \$\endgroup\$