0

I have an eloquent carry that has to filter through 30+ million records. Everything goes well speed wise.. but whenever i try to do a nested filter query it will keep loading and loading until it crashes...

This is the query:

Balance::orderBy('id', 'asc')->maincheck()->simplePaginate(300);

this below is the same query but without the "simplePaginate" behind it

SELECT *
FROM `balances`
WHERE ((
            `balances`.`balanceable_type` = ?
        AND EXISTS(SELECT *
                   FROM `balance_accounts`
                   WHERE `balances`.`balanceable_id` = `balance_accounts`.`id`
                     AND `maincheck` = ?)
    ))
ORDER BY `id` ASC

Does someone have an idea on how to fix this with an other way. I have tried using chunks but it sucks. I have to use eloquent.

Rwd
  • 34,180
  • 6
  • 64
  • 78
Ser
  • 167
  • 1
  • 1
  • 14
  • Are `balanceable_id` and `maincheck` indexed? – Rwd Sep 08 '21 at 16:52
  • Does this answer your question? [Laravel query is very slow when i use 'join'. 30 million records](https://stackoverflow.com/questions/69088391/laravel-query-is-very-slow-when-i-use-join-30-million-records) – Ion Bazan Sep 09 '21 at 08:42

1 Answers1

0

Subselect will be significantly slower in this case as it will execute N queries.

You can try using JOIN instead but make sure there is a foreign key between balance_accounts.id and balances.balanceable_id and balanceable_type is indexed:

SELECT *
FROM `balances`
INNER JOIN `balance_accounts` 
  ON `balance_accounts`.`id` = `balances`.`balanceable_id` 
  AND `maincheck` = ?
WHERE `balances`.`balanceable_type` = ?
ORDER BY `id` ASC

I'm not really sure if this can be implemented with Eloquent filters but you can easily achieve it using Eloquent query builder.

Ion Bazan
  • 723
  • 6
  • 16
  • Can you provide `EXPLAIN` outputs for both queries? Also, what are the indexes and foreign keys in your tables? Have you tried going the other direction (from `balance_accounts` to `balances` using `JOIN` or `EXISTS`? – Ion Bazan Sep 09 '21 at 08:13
  • In this post there is more info about what i have done and my indexes: https://stackoverflow.com/questions/69088391/laravel-query-is-very-slow-when-i-use-join-30-million-records. -And yes i have tried the other way around. Im just going to throw all eloquent away i think, time to replace it with raw sql. – Ser Sep 09 '21 at 08:20
  • Eloquent shouldn't be adding any overhead here and using raw SQL will not solve the problem because you end up with same query in both cases. The goal is to produce a query with an optimal execution plan and it doesn't matter how you are preparing it. I would suggest to experiment in your SQL terminal first and when you're happy with the results, rewrite it in the query builder. Please do note that paginating means that there is another `COUNT(*)` query being executed in order to get the number of pages and this is what may be taking so much time. – Ion Bazan Sep 09 '21 at 08:40
  • Please also avoid creating multiple questions for the same problem. – Ion Bazan Sep 09 '21 at 08:42