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.
addresses address (pk) assets asset_name (pk) balances address (index) asset_name (index)
// 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... ?