Song database query

Question

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_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:

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

Answer 1

I don't know is it help or not (since it's a really complicated query) but it's usually worth caching computed values such as rating and ratings_count and updating them once a day or an hour for example. In the majority of the cases it's enough to provide non-real-time data to the users.

• Federico J.: In addition to @palacsint, I usually fire a DDBB trigger to do this kind of taks, as adding number of votes, add/substract pictures in a gallery, etc.