3
\$\begingroup\$

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
\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Good point palacsint, thanks, that will definitely help. \$\endgroup\$ – user6595 Jan 27 '12 at 15:39
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Chococroc Jul 24 '14 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.