Slow Querying DB

Question

I am currently optimising a system with many connected tables.

The part that I am working on right now is displaying table orders. The problem is that in this table there are also many relations (around 10) which I am querying. The problem itself is in querying that many relations. I have been using Eloquent + with() methods for eager loading but the page is already getting slow, even though it has just under 3 000 orders. The problem is that in table I also print data from 1:N relations (for example a order has many loadings and in the table I print the city of the first loading)

So since it is already getting slow with eloquent and eager loading, I have decided to switch to Query Builder. But it has a few flaws for me which I do not know how to resolve.

The query itself is already huge:

$orders = DB::table('orders')
        ->select([
            //around 25 different selects
        ])
        // nearly 10 left joins on other tables
        ->leftJoin(...)
        // a few when-s with nested where-s, orderBy-s and so on
        ->when(...)
        ->get();

Firstly, I use joins to join together the tables of data, which is nice in case of (table)1:N(orders) but becomes a problem when connecting (orders)1:N(table), because I am not really trying to join all of those records. For nearly all of those I need just the latest or first record.

The first option I decided to use was subquery in select which I quickly realised was a huge mistake since it executes on every row. Then I found a solution by joining a subquery to my table, however the query takes too long (I only connected one like this and query was 8 seconds and I am supposed to join 6 times)

->leftJoin(DB::raw("(SELECT t1.fileable_id, t1.filename FROM files t1 INNER JOIN (SELECT fileable_id, MAX(created_at) AS max_created_at FROM files WHERE fileable_type = 'App\\Order' GROUP BY fileable_id) t2 ON t2.fileable_id = t1.fileable_id AND t2.max_created_at = t1.created_at) AS contractor_files"), 'contractor_files.fileable_id', '=', 'orders.id')

I wanted to ask whether or not there is any solution. I can post the whole query if needed, but it is about 100 lines long and is pretty much summed up

Answer 1

Something is wrong with the query. But this might help the subquery:

INDEX(fileable_type, fileable_id, created_at)

Also, don't you need to include fileable_type in the ON clause?