Query causing huge load ..

[eluser]mvdg27[/eluser]
Hi guys,

I've build a website for a client and apparently there is one query that is causing a huge load. At this moment it's actually stalling the entire website, and the query never finishes either, i.e. after waiting for a very long time it return a blank page.

Now, I know some about databases and queries, but I don't consider myself to be an expert. So, I'm wondering if there are some more knowledgeable people around here, that could take a look at the query, and give me some pointers on how to optimize it.

Code:

$this->db->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');
        
$this->db->join('artists', 'artists.id = songartistDefs.artist_id');
$this->db->join('songartistDefs', 'songartistDefs.song_id = songs.id');
$this->db->join('songstats', 'songstats.song_id = songs.id', 'left');
$this->db->join('songvotes', 'songvotes.song_id = songs.id', 'left');
$this->db->order_by('views', 'desc');
$this->db->limit(40);
$this->db->group_by('songs.id');
$query = $this->db->get('songs');

What the code does, is producing a list of the 40 most viewed songs on a video website. It displays, the name of the artist, the song title, the number of views and it calculates the number grade assigned to the song, by deviding the total of votes by the number of votes.

The code used to work very well, but that was before the website got heavily usage.

Any help or pointers are greatly appreciated!

Michiel

[eluser]GSV Sleeper Service[/eluser]
you might have to add a few indices to your tables. get the SQL that the query generates, stick EXPLAIN in front of it, then run that query in phpmyadmin, you'll see a lot of performance information, and from there'll you'll be able to see what tables/columns need indexing.

[eluser]manilodisan[/eluser]
I agree. I 99% believe it's because you're not using indexes in your tables.

[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