dealing with huge tables

Published 3 weeks ago by Boldozar

im working on polls script i have 3 tables which are polls , options , answers i have 942 poll ,, 10 options for each poll ,, and around 5000 answer for each option in the problem comes with calling data using eager loading the request takes too long ,, any ideas for optimizing the script?

Snapey
Snapey
3 weeks ago (1,036,605 XP)

Thats not huge. Huge is 10s of millions (or more!)

Sounds like though you might be trying to work with too much in memory at once.

Thats about all we can help with without more detail

Boldozar

@Snapey it was huge for me :D what details you may need?

Snapey
Snapey
3 weeks ago (1,036,605 XP)

Well what are you trying to load from the database?

Boldozar

@Snapey im trying to get all polls with all it's options and answers using eager loading,, here is the using Poll::with('options','answers')->get()

Cronix
Cronix
3 weeks ago (783,370 XP)

More than likely this comes down to your database design, and how things are indexed. Are all of your foreign keys indexed? That's the main thing. Are things you are using in where statements indexed? It would help to show the schema of the tables involved.

Cronix
Cronix
3 weeks ago (783,370 XP)

Poll::with('options','answers')->get()

And why would you load 942 polls, along with all of their options/answers in a single request? Normally you'd use pagination and only load like 10 or whatever at a time? That's crazy lol.

Snapey
Snapey
3 weeks ago (1,036,605 XP)

Like I say, trying to load too much into memory. Why do you need all polls in memory at once?

What is the business problem you are trying to solve?

Boldozar

@Cronix yes all foriegn keys are indexed here is a link for the schema https://www.insystems-eg.com/pics/Capture.PNG

Boldozar

@Cronix @Snapey im trying to get all polls and count options and answers for each one

Cronix
Cronix
3 weeks ago (783,370 XP)

Use withCount() then if you only need the count and not the actual data.

$polls = Poll::withCount(['options','answers'])->get();

@foreach ($polls as $poll)
    {{ $poll->options_count }}
    {{ $poll->answers_count }}
@endforeach 

That will save quite a bit, but it would still be best to not load all polls at once like that.

https://laravel.com/docs/5.7/eloquent-relationships#counting-related-models

Boldozar

@Cronix im using datatables so it handle the loading data ,, im not loading all data once :D any way thanks for help

@Snapey Thanks for help

Please sign in or create an account to participate in this conversation.