Alternative to whereIn or optimizing whereIn for large queries

Posted 5 months ago by droplister

I have a database of assets, addresses, and balances. I have a query that will fetch, for a given asset, what other assets addresses also have. So, like affinity assets or related assets.

"Database"

addresses
address (pk)

assets
asset_name (pk)

balances
address (index)
asset_name (index)

AssetsController

    // Addresses That Have This Asset
    $addresses = $asset->balances()->distinct('address')->pluck('address');

    // Assets The Addresses Also Have
    $relatedAssets = \App\Balance::where('asset_name', '!=', $asset->asset_name)
        ->whereIn('address', $addresses)
        ->selectRaw('COUNT(*) as count, asset_name')
        ->groupBy('asset_name')
        ->orderBy('count', 'desc')
        ->take(10)
        ->get();

And this works fine, when I have as many as 10,000 addresses, but there is one case where I have 150,000 addresses and it takes a very long time to execute, I would have to change my server settings to allow it to run longer. I'm okay with queries running for a while because I cache the result for a day, but this one is ultra long.

Is there something I can do beyond indexes (which I already have) to make this work better? I suspect this is a bad use of whereIn. Maybe I should I build a pivot table or... ?

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

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.