wp_bbas performance

FYI I had to deactivate the plugin again. The 24 hour query was starting to impact performance too much.

So my apologies, it looks like you are looking to keep multiple entries per users. That’d make sense for ‘most users ever’ etc. I’m still looking at why performance was going downhill. I’ll let you know if I find a cause or solution.

UPDATE: Here’s a version of the query that runs much faster. .03 sec vs 1.2 sec in my setup. I switched the IN clause to an INNER JOIN. And also removed what looked like a duplicate WHERE clause based on the date calculation.

Note I still get dupes for one user who has 3 records with same timestamp. Normally you can avoid that by doing a GROUP BY on this entire query but you’re also returning the ‘id’ of the record so the grouping wouldn’t work. If you can’t remove ‘id’ from this query you may want to add a uniqueness filter in code before display.

SELECT p.date_recorded as date_recorded, p.id, p.userid, p.status,
w.user_nicename, w.user_login, w.display_name
FROM wp_bbpas AS p
INNER JOIN wp_users AS w ON w.id = p.userid
INNER JOIN (
	SELECT userid, MAX(date_recorded) as dater
	FROM wp_bbpas
	WHERE date_recorded >= NOW() - INTERVAL 24 HOUR
	GROUP BY userid ) AS subq ON subq.userid = p.userid AND subq.dater = p.date_recorded
ORDER BY date_recorded DESC

Hey illiniguy,

First and foremost, I am glad performance has improved. It was a big problem for some sites, and I wanted to correct it. Previously it would be hitting the database constantly to find all users, and it is no longer doing that!

Now, the reason why you have duplicates is you are actually running a slightly older copy of 1.3.1.

I noticed this pesky fellow last night and released a quiet update to the plugin, if you run the following queries everything should be nice and speedy! (basically, userid wasn’t set to unique. I have migrated the data, and since most sites using the plugin won’t have hundreds of members I hoped it wouldn’t be too much of an issue. (Apologies!)

DELETE FROM wp_bbpas
WHERE id NOT IN (SELECT *
FROM (SELECT MAX(n.id)
FROM wp_bbpas n
GROUP BY n.userid) x)

Might be a bit slow, apologies for that. All it is doing is deleting the duplicate data.

Next, make the userid column unique

ALTER TABLE wp_bbpas ADD UNIQUE(userid)

That should then mop up (and prevent) any duplicates from appearing. I will run some tests with your SQL code and more than likely add it into the next release!

(If you are doing that, please finally run the following command else all of your data will be lost in subsequent updates!)

UPDATE wp_options SET option_value = '1.0.2' WHERE option_name = 'bbpress-advanced-statistics-dbversion'

If any other users are reading this, I would actually suggest just installing the latest version… it does it all for you! ??

Please keep me updated on progress. Thank you ??

Thanks for the reply. Is there a reason you don’t bump the version number to 1.3.2, etc.? That’d make it much easier to know when there are updates or which version we have. Especially as I know you’re making a lot of tweaks. I know the number may get weird (1.3.25…) but I’d personally rather have that.

@illiniguy it was purely because it was a file I missed when releasing, not too many people had downloaded it by that point. Plus, I had made plenty of commits that day bumping the plugin continuously… not what I want to do (as it is irritating to end users, it is irritating to other people publishing their plugin).

Regardless, for the future it won’t happen again. It has been a very, very messy release all round. Plenty of lessons were learned.