MusicNet is a social network for music enthusiasts. It allows users to search through 1+ teribyte music database, rate, play and buy music, befriend other users, discover events based on user location, and buy personalized ads.
I worked on planing, documenting, designing, setting up and optimized the MySQL database and developing the dynamic website using PHP, HTML, CSS, and Bootstrap.
Find the songs that have never been played.
Intially, we wrote this using a subquery, but we discovered that this method took to long. The solution was to use a left join or “not in”. See results
select * from Songs S where S.song_id not in(select P.song_id from Plays P);
238064 rows in set (4.55 sec)
For each song that is rated more than 1,000 times, find the song id and the average age of the users who rate the song.
Performance Issue: The inital query written took about 34 seconds to execute
Optimization: Use materialization. Create a table of from Ratings of all songs that have been rated more than 1,000 times
mysql> create table UserRates as( Select song_id, user_id, count(rating) as counter from Ratings group by song_id having (counter>1000));
mysql> select R.song_id, avg(U.age) as avgAge from UserRates R, Users U where R.user_id=U.user_id group by R.song_id
2178 rows in set (0.69 sec)