Bug Query Optimization Needed.

[Wayne Luke]

I have a client using vBCredits Pro on a 3.8.2 vBulletin System.

The issue is this query here:
SELECT t.* FROM credits_transaction AS t LEFT JOIN credits_event AS e ON (t.eventid = e.eventid) WHERE t.userid = 110784 AND t.status = 0 AND (e.eventid IS NULL OR t.timestamp <= (1319759384 - e.delay)) ORDER BY t.timestamp ASC LIMIT 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t index timestamp,userid,userid_stats timestamp 9 479 Using where
1 SIMPLE e eq_ref PRIMARY PRIMARY 4 gathering.t.eventid 1 Using where

Time Before: 0.19004 seconds
Time After: 2.19885 seconds
Time Taken: 2.00882 seconds

Memory Before: 3,271.672 KB
Memory After: 3,272.125 KB
Memory Used: 0.453 KB

I need to know what this query is used for and whether it can be disabled or not. As you can see it is taking over 2 seconds to complete per page load and that is adding significant load to the website. In fact this one query accounts for 80% of page generation time.

Thanks,
Wayne.

 

[Darkwaltz4]

This is an extremely crucial query that needs to run to process transactions so users will actually get awarded anything.

It is, however, poorly indexed, which should be fixed with the following (include your table prefix):

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

 

[Wayne Luke]

Thanks. That index makes a large improvement.

SELECT t.* FROM credits_transaction AS t LEFT JOIN credits_event AS e ON (t.eventid = e.eventid) WHERE t.userid = 110784 AND t.status = 0 AND (e.eventid IS NULL OR t.timestamp <= (1319824788 - e.delay)) ORDER BY t.timestamp ASC LIMIT 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref timestamp,userid,userid_stats,status status 5 const,const 5 Using where
1 SIMPLE e eq_ref PRIMARY PRIMARY 4 gathering.t.eventid 1 Using where

Time Before: 0.32014 seconds
Time After: 0.32739 seconds
Time Taken: 0.00726 seconds

Memory Before: 3,345.430 KB
Memory After: 3,345.883 KB
Memory Used: 0.453 KB

I really appreciate the help.