Welcome Guest, Not a member yet? Register   Sign In
Query causing huge load ..
#1

[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


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



Theme © iAndrew 2016 - Forum software by © MyBB