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

The whole query (in SQL format) and informations about the cardinality of the tables would be very useful to better support you — Gregorio Palamà, Nov 19 '21 at 14:04
Usually its because lack of indexes. There is not enough data. Please, add Explane for both Select operations (like so: explane select ...) and update your post with results for each select — qant, Nov 19 '21 at 14:11
Please include the plain SQL query that is being executed, and also the execution plan. — The Impaler, Nov 19 '21 at 18:27
`, 'contractor_files.fileable_id', '=', 'orders.id')` looks like a gross syntax error. What is intended? — Rick James, Nov 19 '21 at 20:05
To help you with your [tag:query-optimization] question, we need more information. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. — O. Jones, Nov 19 '21 at 20:55

score 0 · Accepted Answer · answered Nov 19 '21 at 20:08

0

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?

answered Nov 19 '21 at 20:08

Rick James

135,179
13
127
222

Slow Querying DB

1 Answers1