0

I have an episodes table and an episode_Listen table, which is a One episode to Many listens
I want to get the 6 episodes with highest listens. I've tried every single solution like the following:

$trending = Episode::where('active', true)
                            ->get()->sortBy(function($podcast) {
                                return $podcast->latestListens;
                            });

Or

$trending = Episode::where('active', true)
                    ->withCount('listens')
                    ->orderBy('listens_count', 'desc')
                    ->get();

Or

$trending = Episode::join('episode_listens', function ($join) {
    $join->on('episode_listens.episode_id', '=', 'episodes.id');
})
->groupBy('episodes.id')
->orderBy('count', 'desc')
->select((['episodes.*', DB::raw('COUNT(episode_listens.podcast_id) as count')]))->paginate(6);

But the execution time always exceeds 6 seconds, because I've around 500k listens records in the database, and they'll go millions in a very short period of time.

Any thoughts?

Thanks in advance

Salim Djerbouh
  • 10,719
  • 6
  • 29
  • 61
MahmutTariq
  • 217
  • 1
  • 4
  • 17
  • Check if your tables are properly indexed. However - For this you need to read the entire `episode_listens` table. That can be slow. You might need to maintain a counter column in the `episodes` to improve the performance. – Paul Spiegel Oct 05 '19 at 12:45
  • 1
    there will be slow if you counting or summing the data from database, the alternative way to fix this you can use follow this post https://stackoverflow.com/questions/43246532/mysql-sum-query-is-extremely-slow – dennisgon Oct 05 '19 at 19:03

0 Answers0