• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query causing huge load ..

#4
[eluser]mvdg27[/eluser]
Yes .. thanks for the tip about doing EXPLAIN in phpmyadmin. Indeed I didn't add indexes to all the tables. With the information from EXPLAIN, I found which.

The queries are much faster now. Though 1 of them still takes 4 seconds to execute, according to the profiler. Does that indicate there is still something to improve here?

The query I'm talking about is this one:

Code:
SELECT artists.id AS artist_id, artists.name AS artist_name, artists.url AS artist_url, songs.id AS song_id, songs.name AS song_name, songs.url AS song_url, songs.datetime AS song_datetime, count(songstats.song_id) AS views, (songvotes.votes / songvotes.num_votes) AS vote FROM (`songs`) JOIN `artists` ON artists.id = songartistDefs.artist_id JOIN `songartistDefs` ON songartistDefs.song_id = songs.id LEFT JOIN `songstats` ON songstats.song_id = songs.id LEFT JOIN `songvotes` ON songvotes.song_id = songs.id GROUP BY songs.id ORDER BY `views` desc LIMIT 40

The other query takes 1.8 seconds, which is somewhat more acceptable.

Code:
SELECT artists.id AS artist_id, artists.name AS artist_name, artists.url AS artist_url, songs.id AS song_id, songs.name AS song_name, songs.url AS song_url, songs.datetime AS song_datetime, count(songstats.song_id) AS views, (songvotes.votes / songvotes.num_votes) AS vote FROM (`songs`) JOIN `artists` ON artists.id = songartistDefs.artist_id JOIN `songartistDefs` ON songartistDefs.song_id = songs.id LEFT JOIN `songstats` ON songstats.song_id = songs.id LEFT JOIN `songvotes` ON songvotes.song_id = songs.id GROUP BY songs.id ORDER BY `song_datetime` desc LIMIT 40

Does anyone have any pointers here? There is also some of the output in phpmyadmin that I don't understand completely:

"Using temporary; Using filesort". Is that a bad thing? Is it preventable?

Thanks in advance for the input. So far I'm happier already Wink

Michiel


Messages In This Thread
Query causing huge load .. - by El Forum - 09-24-2008, 03:20 AM
Query causing huge load .. - by El Forum - 09-24-2008, 03:24 AM
Query causing huge load .. - by El Forum - 09-24-2008, 03:42 AM
Query causing huge load .. - by El Forum - 09-24-2008, 05:19 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.