[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
Michiel
