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 sprJOIN $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_ids from $sTable6.The WHERE clause is dynamically generated depending on what the user is filtering upon.I am specifically concerned about the fact that I am currently dynamically generating the WHERE clause when a user wants to search by genre_id, by looping through a string of comma-separated genre_ids and building the WHERE clause like so:WHERE genre_id LIKE '%6%' OR genre_id LIKE '%3%' OR genre_id LIKE '%8%'This strikes me as inefficient 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: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)?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_namecolumn                                           icon_url  etc....columncolumn$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    Extra1     PRIMARY     <derived4>  ALL     NULL            NULL        NULL    NULL                    2   Using where1     PRIMARY     <derived2>  ALL     NULL            NULL        NULL    NULL                    9   Using where; Using join buffer1     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 filesort4     DERIVED     h           eq_ref  PRIMARY         PRIMARY     4       songbanc_cms.g.song_id  1   Using index2     DERIVED     <derived3>  ALL     NULL            NULL        NULL    NULL                    9   Using temporary; Using filesort2     DERIVED     r           ref     PRIMARY         PRIMARY     4       sp.songsID              2   Using index3     DERIVED     s           index   NULL            PRIMARY     4       NULL                    2   Using index3     DERIVED     p           ref     songs_id        songs_id    4       songbanc_cms.s.songsID  4   Using index
Song database query
performance;sql;mysql