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
$sTablewith 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
WHEREclause 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
WHEREclause so the database doesn't have to use
LIKEsearching 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