slug permalinks can lead to db problems
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:
However, I set my bbpress to use “slugs”, so that topic redirects to:
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?
P.S. In a perfect world, bbpress might automatically index these fields if the keymaster selects “slug” or “name-based” permalinks.
You must be logged in to reply to this topic.