Query now taking ~30 seconds, why?

So, this never happened EVER before. This is really weird too. I can only assume it has to do with counting the views, but even that it is only grabbing 15!

SELECT uploadsNew.id, uploadsNew.title, uploadsNew.genre, uploadsNew.content, uploadsNew.url, SUM(IF(views.ip, 1, 0)) AS views, uploadsNew.approved, SUM(IF(ratingNew.rating = '1', 1, 0)) AS likes, SUM(IF(ratingNew.rating = '-1', 1, 0)) AS dislikes, SUM(IF(commentsNew.status = '0', 1, 0)) AS comments 
FROM uploadsNew 
LEFT JOIN views ON views.id = uploadsNew.id AND views.type = '0' AND views.subtype = '1' 
LEFT JOIN commentsNew ON commentsNew.uploadID = uploadsNew.id 
LEFT JOIN ratingNew ON ratingNew.upload = uploadsNew.id AND ratingNew.type = '0' 
WHERE uploadsNew.type = '1' AND uploadsNew.status = '0' GROUP BY uploadsNew.id 
ORDER BY uploadsNew.approved DESC LIMIT 15

Code (markup):

I have tested with phpMyAdmin and get times around ~30 seconds.


This happened after I changed up the way I get the views for the uploads. Basically before I had a column inside the uploads counting data - I changed this to be an associate table (the 'views' table). Even with this new change, I had quick times when I did:

(uploadsNew.views + COUNT(views.ip)) AS views

Code (markup):

So, basically all I removed was the views column!!


I can run a super fast query (times ~0.09 sec) on:

SELECT uploadsNew.id, SUM(IF(views.ip, 1, 0)) AS views
FROM uploadsNew
LEFT JOIN views ON views.id = uploadsNew.id AND views.type = '0' AND views.subtype = '1'
WHERE uploadsNew.type = '1'
GROUP BY uploadsNew.id
ORDER BY uploadsNew.approved DESC LIMIT 15

Code (markup):

Anyone know why this now is taking so long to query?

 

It is probably an indexing issue with the way you manage your index and ids. Please provide the db structure so it might be helpful to see what exactly is the issue

 

You can use EXPLAIN statement:

After run above query you can see which table take a long time and why

 

Sorry for not getting back faster.

Here is what I found out just by testing and posting on another help forum - problem STILL exists though.

The problem is with the SUMS of the ratings and the views table. When they are done separately the times are normal (0.02xxxx) if not lower. Though, when you combine both the SUMS from the views table and the ratingNew table is when you get times ~20+ seconds.

uploadsNew

commentsNew:

ratingNew:

views:

The EXPLAIN:

 

BUMP - I am STILL having problems with this. I did condense the views table, and got the CRON job to work, so the number of rows searching through is less, and this does work for one thing.


I think my LEFT JOINS are making the SUMS read multiple times over and over again. Is there a better way to structure this? I thought a LEFT JOIN will read in the first table, do what it needs to (grab that information) than read in the next tables one after a time, comparing them TO THE FIRST table?


Can anyone help me out the slightest? I know these tables are doing multiple calls to the SUMS (if the SUM is not in the first LEFT JOIN) because I can see it giving one of them over 12 billion views - I would love to have that, but that is obviously not right. Anyone give me any help? Please and thank you.

 

Did you try this?

 

I already did as in my post above, I don't know which part 'explains' the actual timings of the different queries. Though, the timing is still bad, the new question is why is the SUMS() not doing what I tell them do with the IF statements, as in why are they getting run multiple times when their LEFT JOINS are not filtering out them? If that makes any sense.

Example:
Real Views of Test Content: 10
Rating of Test Content: 0

After SUM: it would be something outrageous like ten times that so like 100+