Full text search is of course still quicker and getting rid of the GROUP BY helps even further. 66% quicker in my case. I’m going to use the following query as my search.
SELECT p . * , MATCH (p.post_text) AGAINST ('test') AS
search_score FROM bb_posts AS p WHERE MATCH
(p.post_text) AGAINST ('test') AND p.post_status = '0'
AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics
AS t WHERE t.topic_status <> '0') ORDER BY search_score
DESC LIMIT 30;
The downside is that it doesn’t include topic titles in the search. You can solve this adding this search to a temporary table, running another search on topic_title, and merging the two tables together. You need quite a bit of PHP code to manage this (particularly getting the NEXT buttons to work correctly), but it’s quite possible.
@sambauers: Let me just give you a couple of observations. Firstly, regarding the first search performed.
SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%test%' AND p.post_status = '0' AND t.topic_status = '0' GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;
The first thing that can be done here is remove the join and replace it with a sub-query. This way the sub-query can be cached, and speed is much improved. Here’s the idential query without the join:
SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p WHERE p.post_text LIKE '%test%' AND p.post_status = '0' AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics AS t WHERE t.topic_status <> '0') GROUP BY p.topic_id ORDER BY p.post_time DESC LIMIT 5
That cuts execution time by 55% on my setup. If you have lots of deleted topics this might not apply, but if you have a huge database you ought to be cleaning the deletes out of it regularly anyway.
_ck_ is also right that ordering by post_id is quicker than by post_time. 17% quicker in my case.
I don’t know – what’s easier for you, downgrading to 2.5.1 or upgrading to an alpha release of bbPress?
Personally, if I needed integration between WordPress and bbPress, I would install the 0.9.0.2 of bbPress and 2.5.1 of WordPress. Alpha software should be used for testing only.
Posts are editable for a configurable period of time after posting, by the original poster. You can post then look at the post and see how it worked.
This plugin also worked with earlier version of bbPress:
https://bbpress.org/plugins/topic/live-comment-preview/#post-813
Nope, there’s not. That’s okay
Testing to see if there is a preview page for posting
Hi I do have a version working(I downgraded bbpress to the last version bbsync worked because I was tired of using a alpha version of bbpress) it’s completely new code, but it’s very basic on the feature set and a bit hacked together, as I could never get the bbpress functions to work in a wordpress plugin.
I’m at a public library at the moment so I don’t have the code with me but I can work on the code a little bit when I get home.
Also please note, this was just made for me and I probably have different needs to what you needed, for example I wanted all wordpress posts to make a new topic so there isn’t options on a per post basis to post to a forum. Also comments don’t replicate between bbpress and wordpress(I was just going to disable commenting and make all users use the topic) but depending on how much time I have tonight I might add this functionality as I don’t see the difficulty in it.
Also F.Y.I. if anyone is interested I do it the úber ghetto way and manually manipulate the database and don’t go through any of the wordpress or bbpress functions so use at your own risk, but I haven’t run into any problems on my test site.
It was WordPress Mu 2.6.1 and BBpress Alpha…..
I have done a few re-installs since then and now am able to log in if I take out the reference to wp-config.php or wp-blog-header.php from my bb-config file.
In any event, it is odd because I had the setup of WordPress 2.6.1 and Bbpress 0.92 on my test server and that was working fine. Now integration has all gone to hell and back…
Remember, the problem with MATCH AGAINST is it will not do partial words or words under 4 characters.
It’s not too hard to replace the search, you just have to decide which way you want to go. The regular expression will at least do 3 character words which I find is more common than you’d imagine.
The problem is that the time for any way without an index is going to increase dramatically once you start adding sorting and other options that cause full table scans. You can see this happen if you try to add a simple option to the regex demo like sorting by reverse post_id (which is a trick that should be a little faster than sorting by date).
SELECT * FROM bb_posts WHERE post_text LIKE '%test%' LIMIT 5 ORDER BY post_id DESC;
and
SELECT * FROM bb_posts WHERE post_text LIKE '%test%' AND REGEXP ':<:%test%:>:' LIMIT 5 ORDER BY post_id DESC;
You might want to test a worse case scenario by using three character nonsense words that will cause a cache-miss like “zzz” and “aaa”. Change them each time so mysql cannot cache the results and give you faster times.
If the above example returns in an acceptable amount of time you can just replace bbpress’s built in search with that simple method. By parsing a query you can also AND words together instead of bbPress’s default OR which to me is incredibly annoying and useless (you’ll notice no major search engine like Yahoo or Google does OR by default).
Oh and looking at a MySQL performance guide for text search, apparently bbPress’s search is using ALL of the slowdown triggers:
order by
group by
and worst of all SQL_CALC_FOUND_ROWS
How long does this query take without the modifiers:
SELECT * FROM bb_posts WHERE post_text LIKE '%test%' LIMIT 5;
because that’s as fast as it can possibly get without an index and should be used as a baseline.
Looking at the original query, I believe they are causing a FULL scan of the entire table to rank and group it first. That’s going to be insanely slow.
You might be able to just use LIKE with a simple sort by post date DESC.
The largest bbPress install I have access to has nearly 100,000 rows and still only takes 0.04 seconds for that trimmed query. The original queries you posted take 0.5 seconds so that’s 10x slower.
@Nekita
Don’t modify your bb-config file until after you have installed bbPress.
Even then I can’t guarantee it will work with Trunk as it isn’t tested for deep integration yet.
If you click on the profile link (the title under your username) you will get a list of all posts you’ve made and threads you’ve started. It’s not search, but it will all be there. I don’t believe the member name is searched by default (could be completely wrong there and haven’t tested it in a long while.) I think it just shows “recent posts” and “relevant posts” (at least in my old version that’s what happens.) If you click the profile link, you get topics started and recent replies.
That roadmap is wildly inaccurate and just guesses.
Not only does 1.0 gut huge sections of code that were perfectly working under 0.9 and need to be replaced but it also replaces entire concepts (like the new object cache). There is a huge amount of work to do and I don’t envy Sam and Michael’s jobs right now. Then it will need lots of testing and debugging.
Then there is plugin stability since everything is in a state of flux. I can’t even keep up with the changes anymore, I’ve stopped updating my plugins until things settle down a bit.
Sam is currently working on trackbacks (pingbacks) now which was suppose to be the big new “feature” for 1.0 IMHO it’s a huge waste of time that could be spent elsewhere in the code but pingbacks on forums was something Matt wanted, so he gets what he wants obviously. However it will be the first feature I delete (not just disable, but delete) since XML-RPC was the #1 security problem with WordPress over the years. It’s also going to be a spammer’s delight.
Actually, the best thing to do is completely replace the search facility. That’s what I do in Super Search. bbPress’s search is very weak, so weak that there wasn’t even a link to it on any page in 0.9 (it’s similar to WordPress’s search, which also sucks, it’s the ugly truth no one seems to talk about).
I assume you are using the bb-benchmark plugin to watch those queries happen (if not, you should be).
Stupid question but you DO have the mysql cache turned on? I only ask because on many server configs (like CPANEL) it’s turned off by default. What does your my.cnf look like? (do a cat /etc/my.cnf in your shell)
Try going into phpmyadmin (or command line) and test that first test query against adding AND post_text REGEXP ':<:%test%:>:' to the query like so:
SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%test%' AND p.post_text REGEXP ':<:%test%:>:' AND p.post_status = '0' AND t.topic_status = '0' GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;
I suspect in the end due to your huge db size you are going to need to completely replace the search functions with something like this http://sphinxsearch.com which has a PHP api. Fortunately in bbPress it’s very simple to hook the internal search and bypass it entirely without even template hacks. A quick google shows that a few WordPress plugins have sphinxsearch support so that would be easy to copy over to bbPress. If you find the keyword “sphinxsearch” on this source for example, you can see how it’s done: http://svn.scriblio.net/plugin/trunk/scriblio.php
I tested this on a very large database under very low load. The standard bbpress search creates two queries, namely:
SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%test%' AND p.post_status = '0' AND t.topic_status = '0' GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;
and
SELECT t.*, MIN(p.post_id) as post_id, GROUP_CONCAT(p.post_text SEPARATOR ' ') AS post_text, 0 AS search_score FROM bb_topics AS t JOIN bb_posts as p ON ( t.topic_id = p.topic_id ) WHERE t.topic_status = '0' AND p.post_status = '0' AND ( (t.topic_title LIKE '%test%') OR p.post_text LIKE '%test%' ) GROUP BY t.topic_id ORDER BY t.topic_time DESC LIMIT 30
I ran both queries five times with different search terms. On my very large database, with no optimisation, the first query took (167s, 104s, 36s, 29.7s, 24s), the second (33s, 21s, 22s, 20s, 21s).
To give you an idea of what I’m talking about:
http://chocolatebydeath.com/forum/
User: Rauko
PW: IIlCuxOmYRpn
This is a test user I just registered. Once you log in with this data, you’ll see that this account is extremely restricted for whatever reason. It’s the same with my own admin account and other user accounts.
Well you’ll have to test to make sure a wikipost isn’t created yet on that topic (and that topic hasn’t been made yet, and they don’t edit the link to change the topic name,link, etc.).
But adding bbcode style parsing to posts is very straightforward. You’ll need to use preg_match_all on the $post->post_text
something like this:
add_filter('post_text', 'make_wiki_links'); // you can also try 'pre_post' which will make it only process the text once during save and not everytime it's displayed
function make_wiki_links($text) {
if (preg_match_all("/[wiki](.*?)[/wiki]/sim", $text, $wiki_links)) {
foreach ($wiki_links[0] as $wiki_link) {
// do whatever you want to each $wiki_link here
}
}
return $text;
}
You’ve got about a dozen problems to handle with this technique, including replacing the [wiki] parts afterwards with another preg_replace, good luck.
I’m curious about this issue as performance problems always intrigue me. You must be using 0.9 as 1.0 has an index on stickies by default.
As far as fulltext search there is a trick I use because of the multiple issues with fulltext (not only speed but fulltext can’t do words less than 4 characters until you customize and rebuild mysql). The trick is to use regex and do a two pass query where you first exclude all the posts without the words and then allow mysql to do a regular scan of the remaining posts.
Query example from my Super-Search plugin:
WHERE post_text LIKE '%".$term."%'" AND post_text REGEXP ':<:".$term.":>:[^']' "
Compare the performance of that against a fulltext search that uses "MATCH post_text AGAINST $term" I don’t have enough data to do a huge benchmark but some simple tests with the cache off shows 0.4 seconds for the trick and 0.9 for the fulltext.
The only downside to the trick is you cannot do partial word searches that way. ie. $term="cat" will only return posts with the exact word “cat” and not “cats” or “category”. But it should be way faster.
Searching a huge number of posts is a non-trivial problem. It’s been known to crush other forums like vbulletin which has fancy code to prevent users from searching too often/too quickly and even disable search temporarily on high server load. Sites like Wikipedia have to go through several technically complex tricks to keep the search fast on that much data.
Many large sites end up using sphinxsearch to replace fulltext search. You could interface it to bbPress via their PHP api.
You can read more workarounds on the mysql fulltext search page (with far more knowledgeable people than me) http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
I successfully integrated bbpress alpha with the latest wordpress. The install is pretty flawless.
Now, I want to pull in the header and sidebar from wordpress. Does anyone have any tips for me on how to do this?
http://www.punaji.com/
It’s a bit hard to understand at first, but given time I think the layout is much more usable than normal message boards. I would appreciate giving it that time, but if you want to know a quick rundown of how things work read on.
The left-most links on the “ribbons” are the categories (art and other). Running down the ribbons from left to right are the latest topics that have been posted in. The topic titles are on the ribbons while the last post of the topic shows beneath, and the username of whomever posted that message. Yeah, that’s about it.
There are quirks still, but they’re being worked on. Any insight on how you found the experience to be would be greatly appreciated.
Hi
I have WP 2.6 installed and the latest svn version (updated yesterday) of bbPress. The two are integrated with each other. I had a look at the users and roles in WP and noted that bbPress had changed all the roles to the bbPress role types. The WP roles are now all gone. The problem is that no one of my users can login, only the first admin user which I created when installed WP and bbPress. If I change another user from member to admin he cannot login.
Can I change back the roles to the default WP roles or do you have any clue what have happened?
Can add bbPress poll into wordpress like Bbpress Latest Discussion and embed wordpress post?
The test registered name is not in my database, just the original keymaster user.
I installed the alpha on its own database. When I test register, I am not sent a password, nor password sent when I try to recover password.
I just finished a test integration of bbpress into word-press and all is working very well with one exception. The static front page for word press has stopped functioning when I add the following to the end of the wp-config.php file:
if ( !defined(‘BBDB_NAME’) )
{
require_once(ABSPATH.’forum/bb-load.php’);
}
If I remove this and the couple of bbpress calls that I’m using which require this, the static front page works again. I think I’m just too tired to find the answer in the source.
Has anyone solved this? BTW — this is WP version 2.5.1 and bbpress 0.9.
Thanks in advance for any help.
