MySQL/Eloquent Query Optimization

It is hard to do guesses without seing the real data... but anyways:

I think the problem is that even if you LIMIT the required rows to 6, you have to read ALL the albums table, because:

• You are filtering them by a non-indexed column
• You are sorting them by an non-indexed column
• You don't know which albums will make the cut (will have a song for required genre). So you calculate all of them, then order by release_date, and keep top 6

If you accessed the albums in a sorted published status and published date, once you get first 6 albums that make the cut, mysql can stop processing the query. Of course, you may have 'bad luck' and perhaps the albums that have genre-6 songs are the oldest-published ones, and thus you will have to read and process many albums anyways. Anyways, this optimization should not hurt, so it is worth trying, and one should expect the data to be somewaht eventy distributed.

Also, as stated on other answers, you don't actually need to access the geres table (abeit this is not probably the worst problem of the query). You may just access genre_song and you may create a new index for the two columns you need.

create index genre_song_id_id on genre_song(genre_id, song_id);

Note that previous index only makes sense if you change the query (As suggested on the end of the answer)

For the albums table, you may create any of those two indexes:

create index release_date_desc_v1 on albums (published, release_date desc);

create index release_date_desc_v2 on albums (release_date desc, published);

Choose the whatever index is better for your data:

• If the percentage of published albums is "low" you probably want to use _v1
• Else, _v2 index will be better

Please, test them both, but don't let both indexes coexist at the same time. If testing _v1, make sure you dropped _v2 and vice versa.

Also, change your query to not use genre table:

SELECT *
FROM `albums`
WHERE EXISTS
    (SELECT *
     FROM `songs`
     WHERE `albums`.`id` = `songs`.`album_id`
       AND EXISTS
         (SELECT *
          FROM `genre_song`
          WHERE `songs`.`id` = `genre_song`.`song_id`
            AND `genre_song`.`genre_id` IN (6)))
  AND `id` <> 37635
  AND `published` = 1
ORDER BY `release_date` DESC
LIMIT 6;

FWIW, I find the following easier to understand, so I would want to see the EXPLAIN for this:

SELECT DISTINCT a.*
  FROM albums a
  JOIN songs s
    ON s.album_id =  a.id 
  JOIN genre_song gs
    ON gs.song_id = s.id 
  JOIN genres g
    ON g.id = gs.genre_id
 WHERE g.id IN (6)
   AND a.id <> 37635
   AND a.published = 1
 ORDER 
    BY a.release_date DESC
 LIMIT 6

In this instance, (and assuming the tables are InnoDB), an index on (published,relase_date) might help.

One thing I noticed is that you don't have to join the genres table, In the following subquery

AND EXISTS
     (SELECT *
      FROM `genres`
      INNER JOIN `genre_song` ON `genres`.`id` = `genre_song`.`genre_id`
          WHERE `songs`.`id` = `genre_song`.`song_id`
              AND `genres`.`id` IN (6))

We can simplify this and following could be the whole query.

SELECT *
FROM `albums`
WHERE EXISTS
    (SELECT *
     FROM `songs`
     WHERE `albums`.`id` = `songs`.`album_id`
       AND EXISTS
         (SELECT *
          FROM `genre_song`
          WHERE `songs`.`id` = `genre_song`.`song_id`
            AND `genre_song`.`genre_id` IN (6)))
  AND `id` <> 37635
  AND `published` = 1
ORDER BY `release_date` DESC
LIMIT 6

Sure you have to optimize your query for quick response time but here is another tip which can rocket your response time.

I had face the similar problem of slow response time and i have managed to reduce it substantially by simply using cache.

You can use redis driver for cache in Laravel, it will save you from querying the database again and again so your response time will automatically be improved,since redis stores the query and its results in key value pair so next time you are making the api call will return the results from cache without querying the database. Using the redis driver for cache will give you one brilliant advantage which i love.

You can use cache tags

Cache tags allow you to tag related items in the cache and then flush all cached values that have been assigned a given tag.So for example you have an api which retrieves posts of user having $id=1 then you can dynamically put data into cache tags so that next time querying the same record will speed up the response time and if you want to update the data in database you can simply update it to cache tags as well.You can do some thing like the following

public $cacheTag = 'user';

// checking if the record exists in cache already then retrieve it from cache
//other wise retrieve it from database and store it in cache as well for next time 
//to boost response time.
$item = Cache::tags([$cacheTag])->get($cacheTag.$id);
       if($item == NULL) {
           if(!$row) {
               $row = $this->model->find($id);
               
           }
           if($row != NULL || $row != false) {
               $item = (object) $row->toArray();
               Cache::tags([$cacheTag])->forever($this->cacheTag.$id, $item);
           }
       }

While updating data in database you can delete the data from cache and update it

if($refresh)
 {
    Cache::tags([$cacheTag])->forget($cacheTag.$id);
 }

You can read more about cache from Laravel's documentation