Slow Querying DB

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

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?

Advertisement

Answer