Should a 50k row query take 59-304 seconds?

[ZomgStuff]

So I come home from classes and I see that bluehost has shut me down because I've been quote on quote using too many of their system resources.

I have a forum that at most has maybe 30 people on (including both members and guests).

I'm looking at the sql_slow_queries log and I see a certain query taking anywhere from 59-304 seconds to execute? This is part of a rating mod that I have installed, and I've never had any problems with it before. Out of nowhere do I see this being a problem in the log. Is this a possible issue with my code, or is this bluehost having server hardware from 1999?

Here's an example
# Mon Jan 12 07:35:53 2009
# Query_time: 305 Lock_time: 0 Rows_sent: 1 Rows_examined: 51233
SELECT r.*, count(r.rating) as count
FROM erate AS r
LEFT JOIN post as p ON(r.pid = p.postid)
WHERE p.userid = 9272
GROUP BY r.rating
ORDER BY count DESC
LIMIT 1

Should it even take more than a second because it says "limit 1"?

[ssslippy]

I would uninstall the mod anything that has to examine that many rows has issues.

[Marco van Herwaarden]

Using a LIMIT will not make any difference in how long it would take to execute the query. It will only shorten the amount of data that needs to be transfered. The LIMIT is only applied after the rest of the query has been executed. SELECT, JOIN, GROUP BY & WHERE are all done first, maybe even using temporary tables to sort.

[ZomgStuff]

Oh okay, because my host claims that the SQL was quering "billions" of rows...Must have been a bug in the code somewhere.

[Marco van Herwaarden]

He might be right. I never said he wasn't, and that actualy just fits in what i have posted.

PS Not literally billions, the number of rows involved are in the example you posted.