Hey guys,

I have a busy bbpress board (http://boards.weddingbee.com) that uses slug-based permalinks. The database has been slow lately, so I started optimizing tonight. While scrolling through the queries in the slow query log, I saw many log entries like this:

SELECT topic_id FROM bb_topics WHERE topic_slug = 'gerbera-daisieswhere-can-i-order-them-wholesale';

Let me back up. The default install for both the old school 0.83 and the new 0.91 has URLs like this:

* http://boards.weddingbee.com/topic.php?id=999

However, I set my bbpress to use “slugs”, so that topic redirects to:

* http://boards.weddingbee.com/topic/need-advice-re-florist-mess-up

The problem is that a slug-based URL requires a database query to translate the ‘slug’ (need-advice-re-florist-mess-up) into a topic_id (999). However, the topic_slug field in the bb_topics table is not indexed. in the bbpress database. Thus, the database needs to scan the entire bb_topics table to find the topic_id!

In short, if you want to use slug permalinks, you should go in and index the topic_slug in your database, or your database will be slow once you have lots of topics. You may also want to index tag (in bb_tags) and forum_slug (in bb_forums). I saw a substantial improvement in performance upon adding these indices via phpmyadmin.

Anyone else run into this issue?

-Bob

P.S. In a perfect world, bbpress might automatically index these fields if the keymaster selects “slug” or “name-based” permalinks.

[@howtogeek Member 16 years, 8 months ago]

That’s a brilliant suggestion… I’ve noticed a weird lag whenever trying to load up a topic, this could be exactly the problem.

Just tested, seems to speed up the query quite a bit, although I’ll have to do some benchmarking to be sure.

[edit]: I just ran this command manually to create the index, I’m not sure if specifying an index length would help or not.

create index ix_name on bb_topics (topic_slug);

[@sambauers Participant 16 years, 8 months ago]

Indexing of those slug columns should be possible and honestly has been an oversight.

We’ll work it into the next release.

It might be possible to write a plugin that forces indexes into those columns.

[@sambauers Participant 16 years, 8 months ago]

So how about the following?:

bb_forums -> Add a unique key to forum_slug

bb_topics -> Add a unique key to topic_slug

bb_users -> Already has a unique key on user_nicename

bb_tags -> Already has a key on tag which should be changed to a unique key

[@sambauers Participant 16 years, 8 months ago]

On further consideration we won’t use unique keys for these (except the existing user_nicename key)

[@bobbyh Member 16 years, 8 months ago]

Thanks for your thoughtful response, sam! That sounds great to me!

FYI, I also came across this query repeatedly in my slow query log:

SELECT SQL_CALC_FOUND_ROWS * FROM bb_topics WHERE topic_status = 0 AND forum_id = 1 AND topic_sticky = 0 ORDER BY topic_time DESC LIMIT 40, 20;

topic_sticky isn’t indexed (in either 0.83 or 0.91, I checked both). Should it be? It’s a tinyint(1), so indexing it should cost very little while improving performance a lot, I think?

[@sambauers Participant 16 years, 8 months ago]

Do you use mysql query cache? The query you are talking about would benefit from query caching as it is hit often.

[@bobbyh Member 16 years, 8 months ago]

Yeah, I have a large mysql query cache on a dedicated server with 1GB of RAM that only runs mysql.

Doesn’t the query cache get thrown away whenever the underlying table updates? On a popular forum, I think the bb_topics table changes fairly often, so I guess that’s why the query appears a lot in my logs.

[@sambauers Participant 16 years, 8 months ago]

I think, but I’m not sure, that a combination index of forum_id, topic_status and topic_sticky would be better to fix this…

What do you think?

[@bobbyh Member 16 years, 8 months ago]

Sam, yes, you’re totally right, sorry I wasn’t thinking. mysql can only use one index per query, and a multi-column index is much more restrictive for this common query, and hence much faster. This will really speed things up, so I’ll add these indices tonight (when server load goes down). Thanks!

I also think it’s worthwhile to add this multi-column index to the core. What do you think?

EDIT: I was too curious, so I added a 3-column index just now. When I did an EXPLAIN statement on my mysql 4.1 database, this 3-column index was listed as a possible_key, but not used as a key. The query with a 3-column index thus didn’t use an index and required a “where” and a “filesort” (for topic_time). I then added topic_time to the index, making this a 4-column index, and mysql started using the 4-column index as the key, which also eliminated the slow “filesort”. This is the probably the most common query bbpress does, so making it faster is very exciting!

[@bobbyh Member 16 years, 8 months ago]

Note: if your DB is slow and you’d like to try this optimization, you must add the columns in the right order while making this multi-column index:

1. topic_status

2. forum_id

3. topic_sticky

4. topic_time

The order of the columns in the index needs to be the same as the order of the fields in your query, as explained here.

[@sambauers Participant 16 years, 8 months ago]

@bobbyh

Do you have any data as to how much this improved things for you?

[@bobbyh Member 16 years, 8 months ago]

Our bbpress forum does roughly 20-25k pageviews per weekday. We also have a WordPress blog (with caching) that does around 75k pageviews per weekday. The boards and blog are integrated, and share the same database.

The database is the only thing that runs on a (mt) dedicated server with 1GB of RAM (the Extreme package), and its my.cnf is a slightly tweaked version of a my-huge.cnf.

The database was really running ragged a week ago, with a load average between 2.5 and 4.0. The first change I made was described in the first post (indexing topic_slug in bb_topics, tag in bb_tags and forum_slug in bb_forums). That really helped a lot, because on a popular forum, there are apparently enough new topics that the query cache gets invalidated frequently. By making this query a lot more efficient with an index, the load average fell to between 0.4 and 0.7.

Then, after adding the 4-column index, the load average plunged to between 0.03 and 0.20. It’s pretty amazing.

Indices are AWESOME!

[@sambauers Participant 16 years, 8 months ago]

https://trac.bbpress.org/changeset/1428

[@bobbyh Member 16 years, 8 months ago]

Sweet!

Thanks, Sam!

[@jeffmax Participant 11 years, 1 month ago]

Hey There,
Is there a more up-to-date suggestion regarding BBPress and Indexes? I imported a huge forum from PHPBB (over 1M posts + 100K users) and it’s very slow! Any suggestions on how to optimize all of the tables?
Thanks!

[@netweb Keymaster 11 years, 1 month ago]

Closing this topic as it relates to the old standalone bbPress v0.9/v1.2x version of bbPress.

None of this information is valid for the current bbPress v2.x Plugin for WordPress.