ionik Posted October 9, 2008 Share Posted October 9, 2008 Yes i have read the sticky.... I got this SQL Statement it is fairly simple and does what it needs to SELECT DISTINCT ch.channelId, ch.name, CEIL( SUM( vr.rating ) / COUNT( vr.rating ) ) as averageRating, COUNT( vt.videoId ) as viewCount, COUNT( cm.commentId ) as commentCount, COUNT( vi.videoId ) as videoCount FROM '.CHANNELS.' ch LEFT JOIN '.VIDEO_RATINGS.' vr ON vr.channelId = ch.channelId LEFT JOIN '.VIDEO_COMMENTS.' cm ON cm.channelId = ch.channelId LEFT JOIN '.VIDEOS.' vi ON vi.channelId = ch.channelId LEFT JOIN '.VIDEO_TRACKING.' vt ON vt.channelId = ch.channelId GROUP BY ch.channelId ORDER BY averageRating DESC LIMIT '.$start.', 10 the problem that i have is that when there is a few entries in the DB it runs great once i add some data ( thousands of rows to each of those tables it completely runs out and just times out the query ) what could be the problem here? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 9, 2008 Share Posted October 9, 2008 Do you need LEFT JOINS as opposed to normal INNER JOIN? Quote Link to comment Share on other sites More sharing options...
ionik Posted October 10, 2008 Author Share Posted October 10, 2008 when run as SELECT DISTINCT ch.name, ch.channelId, ROUND( SUM( vr.rating ) / COUNT( vr.rating ), 2) as averageRating, COUNT( vt.trackId ) as viewCount, COUNT( cm.commentId ) as commentCount, COUNT( vi.videoId ) as videoCount FROM '.CHANNELS.' ch INNER JOIN '.VIDEO_RATINGS.' vr ON vr.channelId = ch.channelId INNER JOIN '.VIDEO_TRACKING.' vt ON vt.channelId = ch.channelId INNER JOIN '.VIDEO_COMMENTS.' cm ON cm.channelId = ch.channelId INNER JOIN '.VIDEOS.' vi ON vi.channelId = ch.channelId GROUP BY ch.channelId ORDER BY ch.latestVideo DESC LIMIT '.$start.', 10 returns no results but SELECT ch.name, ch.channelId, ROUND( SUM( vr.rating ) / COUNT( vr.rating ), 2) as averageRating, COUNT( vt.trackId ) as viewCount, COUNT( cm.commentId ) as commentCount, COUNT( vi.videoId ) as videoCount FROM '.CHANNELS.' ch LEFT JOIN '.VIDEO_RATINGS.' vr ON vr.channelId = ch.channelId LEFT JOIN '.VIDEO_TRACKING.' vt ON vt.channelId = ch.channelId LEFT JOIN '.VIDEO_COMMENTS.' cm ON cm.channelId = ch.channelId LEFT JOIN '.VIDEOS.' vi ON vi.channelId = ch.channelId GROUP BY ch.channelId ORDER BY ch.latestVideo DESC LIMIT '.$start.', 10 returns the desired result but takes about 2 1/2 minutes to run Quote Link to comment Share on other sites More sharing options...
ionik Posted October 10, 2008 Author Share Posted October 10, 2008 Heres a better explaination the statement is as follows SELECT ch.name, ch.channelId, ROUND( SUM( vr.rating ) / COUNT( vr.rating ), 2) as averageRating, COUNT( vt.trackId ) as viewCount, COUNT( cm.commentId ) as commentCount, COUNT( vi.videoId ) as videoCount FROM channels ch LEFT JOIN videoRatings vr ON vr.channelId = ch.channelId LEFT JOIN videoTracking vt ON vt.channelId = ch.channelId LEFT JOIN videoComments cm ON cm.channelId = ch.channelId LEFT JOIN videos vi ON vi.channelId = ch.channelId GROUP BY ch.channelId ORDER BY ch.latestVideo DESC LIMIT 0, 10 and it runs fine for explain id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE vt system channelId NULL NULL NULL 0 const row not found 1 SIMPLE ch ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 1 SIMPLE cm ref channelId channelId 4 mehype.ch.channelId 310 1 SIMPLE vi ref channelId channelId 4 mehype.ch.channelId 63 1 SIMPLE vr ref channelId channelId 4 mehype.ch.channelId 325 but once I run it through the database it will completely slow down and just kill the server....and its a dedicated server....resulting in a restart every time i try to run it the tables don't have that much data at all the videos table has about 630 rows....the ratings has about 3,200 rows, the channel table is only 10 items and the tracking table is completely empty... Any suggestions on this? As I am now stumped by this, and Cannot go anywhere. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 10, 2008 Share Posted October 10, 2008 Do you have indexes on all those id columns in the various tables? Quote Link to comment Share on other sites More sharing options...
ionik Posted October 13, 2008 Author Share Posted October 13, 2008 Yes i do Quote Link to comment Share on other sites More sharing options...
fenway Posted October 13, 2008 Share Posted October 13, 2008 Yes i do the first two NULL keys suggest otherwise. Quote Link to comment Share on other sites More sharing options...
ionik Posted October 13, 2008 Author Share Posted October 13, 2008 Yes i do the first two NULL keys suggest otherwise. the 1st table has no data in it..... the 2nd table is the table that only has the channelid and the primary table where everything is being sorted by... Quote Link to comment Share on other sites More sharing options...
Barand Posted October 13, 2008 Share Posted October 13, 2008 I've improved on your 2.5 mins without bringing the server down [pre] mysql> SELECT ch.channelid, ch.name, A.commcount, B.trackcount, -> C.vidcount, D.avrate -> FROM channels ch -> LEFT JOIN -> (SELECT channelid, COUNT(*) as commcount FROM `videocomments` -> GROUP BY `channelId`) as A -> USING (channelid) -> LEFT JOIN -> (SELECT channelid, COUNT(*) as trackcount FROM `videotracking` -> GROUP BY `channelId`) as B -> USING (channelid) -> LEFT JOIN -> (SELECT channelid, COUNT(*) as vidcount FROM `videos` -> GROUP BY `channelId`) as C -> USING (channelid) -> LEFT JOIN -> (SELECT channelid, AVG(rating) as avrate FROM `videoratings` -> GROUP BY `channelId`) as D -> USING (channelid); +-----------+---------------------+-----------+------------+----------+--------+ | channelid | name | commcount | trackcount | vidcount | avrate | +-----------+---------------------+-----------+------------+----------+--------+ | 1 | Computers | 204 | NULL | 37 | 3.0355 | | 2 | Internet Technology | 346 | NULL | 64 | 3.0224 | | 3 | Cars | 712 | NULL | 143 | 3.0867 | | 4 | Food | 303 | NULL | 62 | 2.8708 | | 5 | Chinese Restaruants | 273 | NULL | 53 | 2.8918 | | 6 | Car Insurance | 205 | NULL | 41 | 3.0924 | | 7 | Trucks | 280 | NULL | 62 | 3.0606 | | 8 | MotorCycles | 439 | NULL | 98 | 2.9721 | | 9 | Computer Software | 166 | NULL | 39 | 2.8333 | | 10 | Video Games | 176 | NULL | 35 | 3.0952 | +-----------+---------------------+-----------+------------+----------+--------+ 10 rows in set (0.02 sec) [/pre] results need verifying Quote Link to comment Share on other sites More sharing options...
ionik Posted October 14, 2008 Author Share Posted October 14, 2008 TY Works Great Quote Link to comment Share on other sites More sharing options...
fenway Posted October 14, 2008 Share Posted October 14, 2008 Nice... didn't even realize that was the point of the query ;-) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.