Alternative to whereIn or optimizing whereIn for large queries

Posted 8 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.

Reply to

Use Markdown with GitHub-flavored code blocks.