[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
