[Fixed] Who Voted? Option in Admin CP takes 3 minutes to load

[vBadvanced]

For quite a while now I've noticed that it would always take forever for the "Who Voted" link to load when trying to see who voted on a poll. I finally decided to play around with the code and found another way to do the query that loads pretty much instantly. It's basically just reversing the order in which the tables are called, but it made a world of difference. When I checked the load time before this change it took 168 seconds to load, but afterwards it was down to about 0.3. Here's what I did to change the query...
In admin/thread.php I just changed this:

$polls = $DB_site->query("
SELECT poll.pollid, poll.question, thread.title
FROM poll
LEFT JOIN thread USING(pollid)
ORDER BY thread.dateline DESC
");
To:

$polls = $DB_site->query("
SELECT thread.title, poll.pollid, poll.question
FROM thread
LEFT JOIN poll ON (thread.pollid=poll.pollid)
WHERE thread.open<>10 AND thread.pollid!='0'
ORDER BY thread.dateline DESC
I'm not sure if this only happens when you have a fairly large number of threads and polls (I have 28,000 threads & 400 polls on my forum), but I've also noticed a problem before with a different polls script where it was extremely slow if the poll table was selected before the thread table.

[Steve Machol]

Not sure this qualifies as a 'bug', perhaps a code optimization maybe.

[vBadvanced]

I wasn't too sure if it would be considered a bug at first, but after thinking about it, with the speed and performance of other vBulletin pages, I would think 3 minutes to load a page should be. I've experienced this problem pretty much since my board started growing rather large, and I've been on several different servers since, so I wouldn't think it's particular to one version of PHP, MySQL, or anything like that.

[DWZ]

Yeah, same problem on one of my bigger forums. I remember a thread ages ago regarding this where someone suggested to use ;continue or something into the code somewhere?

Anyway, a dev ended up saying they wouldn't change it for some reason which I can't remember

[Stadler]

See http://www.vbulletin.com/forum/showthread.php?threadid=58301

But as Scott has stated, this won't be fixed in vB2, because the is no real need to add an index for the sole purpose of speeding up a feature of the admin-cp that is rarely needed.

[vBadvanced]

There's a big difference in the fix I posted and yours Stadler. All mine does is reverse the query a little and it seems to solve the problem without any changes to the database. ;)

[Stadler]

Ooh, sorry, I haven't seen that. I should have paid more attention to the first post instead of the others.

Well, I've tried the fix and it works fine for me. With and without the extra index :)

[Xenon]

well i remember posting something like that for the update counter mechanism which increased the speed also.

as it's not classified as a bug and therefore not needed they didn't change the code, because all changes on vb2 should just be bugfixing and not optimizing.

so i don't think this would be changed here.

[Quizor]

Not sure this qualifies as a 'bug', perhaps a code optimization maybe.

I would call it a reliability fix.

I've noticed the same and the effect is pretty bad. It results in a temporary WSOD that lasts for few minutes because the database can't keep up with all the queries as you can imagine. This will also fill my mailbox with errors. :-)

I mean, this fix is easy to apply to the 2.3.x tree and is definately worth fixing because it bothers your customers.

[Steve Machol]

Well I did move this to the Bugs forum so a Dev could take a look. :)

[Scott MacVicar]

fixed now.