Song database query optimization

I am relatively inexperienced with MySQL and have a query which to my eyes appears relatively complex:

SELECT SQL_CALC_FOUND_ROWS
    songsID, song_name, artist_band_name, author, song_artwork, song_file,
    genre, song_description, uploaded_time, emotion, tempo,
    user, happiness, instruments, similar_artists, play_count,
    projects_count,
    rating, ratings_count, waveform, datasize, display_name, user_url, genre_id,
    IF(user_ratings_count, 'User Voted', 'Not Voted') as voted 
FROM (
        SELECT  
            sp.songsID, projects_count, 
            AVG(rating) as rating,
            COUNT(rating) AS ratings_count,
            COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count

                FROM (
                    SELECT songsID, COUNT(*) as projects_count
                    FROM $sTable s
                    LEFT JOIN $sTable2 p ON s.songsID = p.songs_id

                    GROUP BY songsID) as sp

            LEFT JOIN $sTable3 r ON sp.songsID = r.songid


            GROUP BY sp.songsID) as spr


JOIN $sTable s USING (songsID)
LEFT JOIN $sTable5 q ON s.user = q.ID   
LEFT JOIN (
            SELECT g.song_id, GROUP_CONCAT(g.genre_id SEPARATOR ',') as genre_id
            FROM $sTable6 g   
            JOIN $sTable h ON h.songsID = g.song_id       
            GROUP by h.songsID) as gs 
            ON s.songsID = gs.song_id 

Essentially, this query collects data from several different tables about a list of songs.

• The song table itself is $sTable with the other tables containing various related information such as ratings, projects, uploaded user information etc.
• The final part of the query collects a comma separated list of genre_id's from $sTable6.
• The WHERE clause is dynamically generated depending on what the user is filtering upon.

The part I am specifically concerned about is that currently I am dynamically generating the where clause when a user wants to search by genre_id, by looping through a string of comma seperated genre_id's and building the WHERE clause like so:

WHERE genre_id LIKE '%6%' OR genre_id LIKE '%3%' OR genre_id LIKE '%8%' etc...etc...

This strikes me as ineficient but given the dynamic nature of this specific app I have been unable to come up with a different solution that compares directly using '='.

Therefore, this is actually two questions in one:

1. Is there any way to improve the performance of this query overall. (Any comments on on index schemes or a way of simplifying the query itself are most welcome.)

2. Is there a better way of performing the dynamic WHERE clause so the database doesn't have to use LIKE searching through comma separated strings?

The table in question, $sTable6 is simply a link table for a many-to-many relationship:

$stable (songs)        $stable6 (genres_link)    $sTable7 (genres)
songsID*               genre_id**                genre_id**
column                 song_id*                  genre_name
column                                           icon_url  etc....
column
column

$sTable 7 is not used in the above query at all and does not need to be.

I get the following returned via MySQL EXPLAIN when using a simple 2 genre_id WHERE clause. (with only 9 rows of songs in total, I will be testing large datasets soon)

id  select_type table       type    possible_keys   key         key_len ref                     rows    Extra
1   PRIMARY     <derived4>  ALL     NULL            NULL        NULL    NULL                    2   Using where
1   PRIMARY     <derived2>  ALL     NULL            NULL        NULL    NULL                    9   Using where; Using join buffer
1   PRIMARY     s           eq_ref  PRIMARY         PRIMARY     4       gs.song_id              1   
1   PRIMARY     q           eq_ref  PRIMARY         PRIMARY     8       songbanc_cms.s.user     1   
4   DERIVED     g           index   PRIMARY         PRIMARY     8       NULL                    6   Using index; Using temporary; Using filesort
4   DERIVED     h           eq_ref  PRIMARY         PRIMARY     4       songbanc_cms.g.song_id  1   Using index
2   DERIVED     <derived3>  ALL     NULL            NULL        NULL    NULL                    9   Using temporary; Using filesort
2   DERIVED     r           ref     PRIMARY         PRIMARY     4       sp.songsID              2   Using index
3   DERIVED     s           index   NULL            PRIMARY     4       NULL                    2   Using index
3   DERIVED     p           ref     songs_id        songs_id    4       songbanc_cms.s.songsID  4   Using index

Thanks for any suggestions/comments.