1
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ What is the output of EXPLAIN SELECT for that SQL query? \$\endgroup\$ – 200_success Jun 14 '17 at 7:08
  • \$\begingroup\$ @200_success Thanks, I updated my question to include the explains. \$\endgroup\$ – Citizen Jun 14 '17 at 20:07
  • \$\begingroup\$ @Citizen to a casual reader the query looks almost incomprehensible - quite apart from your question: why not break it down and make it entirely more readable? \$\endgroup\$ – BKSpurgeon Jun 22 '17 at 14:43
1
\$\begingroup\$

I'm going to start by answering your main questions and then side rail into what my (very different) solution would be.

should you add indexes to speed this up?

You could try. That is (possibly) one solution. It may not be an easy solution. It may or may not be possible. At the very least you will have to rewrite part of your query as MySQL cannot index an OR condition (instead you have to use a UNION).

should you break this up into multiple queries and combine/filter in PHP?

I really doubt this will help you at all. If the overall query is slow then breaking it into small parts and combining them together in PHP will probably just be more slow. There are only some very limited use-cases where taking your MySQL logic and moving it into PHP is going to do anything other than slow things down considerably. You'll just have to make more queries (with the associated back-and-forth between MySQL and PHP) while still doing unindexed searches on very large tables. I don't think this option is going to get you anywhere.

Should you take a different approach?

Yes. A completely different approach. But it won't be easy. You have two large issues with this chunk of code:

  1. It is impossible to read.
  2. You are effectively optimizing writes at the expense of reads.

In detail:

Point 1 In this case I think that first point is enough to merit approaching this problem from a completely different perspective. While this query certainly demonstrates that you have a very thorough understanding of MySQL, it is also nearly impossible to read. In essence, it is far to clever. What happens if the rules that determine which photos to display change in 6 months? Especially if someone else is in charge of this? I would bet a lot of money that the odds of success are going to be very, very low. It doesn't matter if this query does exactly what you need if it can't easily adapt when "what you need" changes. Obviously you (or others) may disagree, but I think, just from looking at your query, that you need to start over.

Point 2 I would summarize the underlying issue and say that you have optimized writes over reads, which is usually the exact opposite of what you want to do. What I mean by that is that your overall system architecture makes it really easy to add/update photos, but very difficult to read your database and get back the information you need about those photos.

From what I gather you have 5 or so tables in total: 3 main tables (orders, athletes, and photos) and 2 mapping tables for many-to-many relationships (order_photos and athlete_photos). This is easy and straight-forward: you insert orders, athletes, and photos normally, and if you need to connect them you just drop a record in a mapping table. Conceptually inserting data into your system is very easy, and probably very fast. However, your data is not being stored in the way such that you can get the information that you need out easily. That much is clear from the gigantic SQL query you need to support your read logic. As a general rule of thumb in these kinds of web applications, your application is going to be doing far more reading than writing, so it really needs to be the reading of your table that is optimized: both in terms of performance and in terms of "cognitive load".

So what do you do? Presuming that this "query" that you are running is an important part of your system that isn't going to just disappear with changing user requirements in a week, you need to redesign the entire system so that you can get the data you need out quickly and easily. The 5 tables you currently have can stay. The part you need to add is a table that effectively caches your data so you can quickly get out your results on with a simple join on an index, and update the logic for your write operations so that said table is kept properly populated at all times.

It's actually not as crazy as it might sound (although your specific one is a very complicated problem to solve), and a simple example may help give you an idea of what you mean. So if you'll allow me:

We had to solve a similar (much much simpler) problem recently. We wanted a user to come to a page and see all records that might be associated with them in many different ways. They had to see records that were assigned to them, that were assigned to anyone on their team, or were assigned to a direct subordinate. The simple solution would be to have a column for each of those relationships in the record: assinged_to_id, team_id, supervisor_id. Then you could make a big query like:

SELECT * FROM records WHERE assinged_to_id=@MY_ID OR team_id=@MY_TEAM_ID OR supervisor_id=@MY_ID

This would have been very easy and we could have moved on, but would have quickly run into trouble after about 10K records because you can't index an OR query (and you can't sort efficiently on a UNION query). So the solution was to add another table to denote permissoin-to-view. Let's call it record_access, and it has two main columns: record_id and user_id. Whenever a record is created PHP drops the user id of the assigned person in record_access. It then finds all team members and puts their user ids in record_access, and it finds the supervisor and puts their user id in record_access (we also still keep track of assigned_to_id, team_id, and supervisor_id in the main table, which helps update the record_access table when things change). If the rules on who gets to access the records changes, I just update that bit of code that populates the record_access table, which is actually very straight-forward.

This makes updating the record is more difficult (both in terms of lines-of-code and performance) compared to your current solution, which is optimized for writing. However, it isn't that difficult to keep it all straight, especially with some good comments and a well thought-out code structure. To do something equivalent in laravel you could make use of their event listeners to add in your additional logic without mucking with all of the code that is already there and already works. However, as a result of doing all the hard work up-front, it is now really easy to read. My SELECT query went from an impossible to index double OR condition to a much simpler (and very easy-to-index) single join:

SELECT * FROM record_access JOIN records ON records.id=record_access.record_id WHERE record_access.user_id=@MY_ID

Final note

To be clear, the change I am suggesting is not small, and what you are trying to do is already plenty complicated. What it really boils down to though is overall application performance and long-term maintainability. Complicated things are going to be complicated no matter how you do it. When it comes to large databases though, the trick is to shuffle around the complicated parts in a way that maximizes your overall system performance. Sometimes that means approaching problems from a completely different perspective. In your case indexes might be the solution if you can make it happen and it isn't worth it for you to rewrite PHP to accommodate your MySQL performance issues. Then again, these alternate kinds of answers are often the necessity for long term solutions, both in terms of MySQL performance and application code that can be easily understood and maintained.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.