0

I have got an big query that basically just adds a couple of with()'s, whereBetween() and orderBy. At the end i use a simplePaginate(500) because i don't want to call all 30mil records obviously.

Problem is that whenever i use a join than it will take couple minutes to load it, but when i dont use it than it only takes 2 seconds.

my query->join:

        $query
            ->join('balance_accounts', 'balances.balanceable_id','=', 'balance_accounts.id')
            ->join('currencies', 'currencies.id', "=", 'balance_accounts.currency_id')
            ->join('balance_types', 'balance_types.id', "=", 'balance_accounts.balance_type_id')
            ->select("balances.*", "currencies.short_name", "balance_types.name");

i have put indexes on every column. Maybe im putting indexes wrong because it's foreign, i have no idea..

here are my index that i have put on table 'balances': enter image description here

 

here are my index that i have put on table 'balance_accounts': enter image description here

here my index for 'currencies': enter image description here

Ser
  • 167
  • 1
  • 1
  • 14
  • My first question would be, why do you want to get 30 million records at once? – Gert B. Sep 07 '21 at 12:42
  • @GertB. updated answer. – Ser Sep 07 '21 at 12:43
  • Please share more details, like the execution plan of that query. I would assume that you haven't added indexes for the relevant columns – Nico Haase Sep 07 '21 at 12:43
  • @NicoHaase what do u mean with execution plan? what i want to do is filter data then paginate it and then send it to Vue in a api call. – Ser Sep 07 '21 at 12:46
  • Did you paginate the query or the collection? – Gert B. Sep 07 '21 at 12:46
  • @GertB. the query. – Ser Sep 07 '21 at 12:48
  • are you using eloquent queries or raw queries? – Rushikesh Ganesh Sep 07 '21 at 13:03
  • @RushikeshGanesh eloquent. – Ser Sep 07 '21 at 13:03
  • I think the more optimized the solution is to get chunks of data at a time using Query Builder its more convenient way to handle the data – Rushikesh Ganesh Sep 07 '21 at 13:05
  • @RushikeshGanesh yes that may be so. But then i still have to use join and orderby. and those 2 dont seem to work together quiet well as i explained. – Ser Sep 07 '21 at 13:08
  • instead of join use relations – Rushikesh Ganesh Sep 07 '21 at 13:12
  • https://laravel.com/docs/6.x/queries#chunking-results – Rushikesh Ganesh Sep 07 '21 at 13:20
  • @ChrisServ are you ordering by columns from joined tables? Or only from main table? – Autista_z Sep 07 '21 at 13:21
  • @Autista_z main table. – Ser Sep 07 '21 at 13:26
  • 2
    Then replace Joins with Eloquent relationships (multiple queries under the hood). Join operation is very resource expensive. With 30 milions records it will be very slow allways. If you dont want to use cache, temporary tables, DB views or other workaround. – Autista_z Sep 07 '21 at 13:29
  • @Autista_z Ok so u recommended using DB instead eloquent. Even if i have 500 million records? which i actually have.. Or shouldn't i even be using Laravel for such big stuff... – Ser Sep 07 '21 at 13:39
  • See https://stackoverflow.com/questions/59981047/how-to-execute-an-explain-select-on-a-laravel-builder for advice about how to get the execution plan of your query. That might help to inspect which indexes are missing – Nico Haase Sep 07 '21 at 13:56
  • Also, having indexes on **all** columns is overkill and won't really help, see https://stackoverflow.com/questions/39987543/is-it-correct-to-index-all-columns-in-a-mysql-database/39987929 - after all, that will also negatively impact your performance! – Nico Haase Sep 07 '21 at 13:57
  • @NicoHaase ok i will keep it in mind. but it has worked ok till now. i just wanted to understand why joins dont seem to be working with orderby but it does work with where/with/whereBetween – Ser Sep 07 '21 at 14:02
  • What do you mean by "not working"? Does the query return incorrect data? Or is this a pure performance issue (then keep in mind what a `JOIN` does, and how you could resolve this problem through inspecting the execution plan) – Nico Haase Sep 07 '21 at 14:18
  • @ChrisServ If you order only by main table, and not by tables from joins, then instead of joins use Eloquent relationships: `$balances = Balance::with('account.currency', 'account.type')->whereBetween(...)->orderBy('column')->simplePaginate()` – Autista_z Sep 07 '21 at 14:20
  • Please provide the generated SQL; it will help us who are Laravel-challenged to help you. – Rick James Sep 07 '21 at 15:53
  • Pagination is best done if you can "remember where you left off". I think `simplePaginate` uses the inefficient `OFFSET`. – Rick James Sep 07 '21 at 15:54
  • 3rd party packages often get in the way of using the full potential of the underlying database engine -- especially for 30M-row tables. – Rick James Sep 07 '21 at 15:58

0 Answers0