Bug MySQL 5.1 painfully slow query on all pages

[Munkii]

I did a quick search and didn't see anyone else mention a problem with MySQL 5.1 (in this case, 5.1.56), so hopefully I can get some support on this issue =)

The problem I'm having is when I upgrade MySQL from 5.0 > 5.1 on my VPS, a query that's run on every page from vBCredits takes a lot longer to complete. Here's my example:

Time taken to load forum index with vBCredits enabled:
Page Generation 5,90723 seconds

Time taken to load forum index without vBCredits enabled:
Page Generation 0,52092 seconds


Screenshots from debug mode:

Disabled:


Enabled:


Slow query:


Any ideas? I'd rather stay up to date with my software than downgrade back to 5.0. =)


Edit: I did check the table, and it is quite large.. but even as large as it is, it still didn't add much time to the page generation when using 5.0. I've downgraded before because pages were a lot slower, and once back on 5.0 everything worked great again. This time, I intended to find the problem behind it being slower, and that's why I'm sure it's only slower after upgrading to 5.1.56.

Edit 2: I figured with the query you wouldn't need this, but I pinpointed the plugin causing the problem to "vBCredits II - Apply Event" on the hook "parse_templates"

 

[Darkwaltz4]

this was fixed for pro users, but here it is for the lite ones
You'll have to run this query:

ALTER  TABLE `credits_transaction`  ADD  INDEX (`status` ,  `userid` ,  `timestamp`)

Be sure to add your table prefix if using one.
Give it a couple minutes, and see if the CPU load and/or rendering speed of your site has improved.
Let me know!

 

[Munkii]

Much, much better! Thanks =)

 

[Ozzy47]

Great glad that helped.