http://bbpress.org/forums/ bbPress support forums Tag: search en Tue, 02 Dec 2008 22:24:04 +0000 http://bbpress.org/forums/topic/advanced-search-options-where-and-what-are-they#post-21220 Tue, 02 Dec 2008 20:25:50 +0000 _ck_ 21220@http://bbpress.org/forums/ <p>Eh don't defend bbPress - it's the same problem with WordPress. They've never had a good search because writing a good search is hard and takes time from other needed things.</p> <p>I haven't released the search because after working on it for a week it became so overly complex that debugging parts of it was taking way too much time. When that happens I get mad and then usually re-write things from scratch.</p> <p>I think my biggest mistake was I tried to make the layout template-able, so you could move stuff around. That added way too much code and logic problems. And then there was the wordpress awareness, more code.</p> <p>I might just make a simpler version of it. I may just copy some of the vbulletion search layout since it's very logical. Maybe I'll have something by the end of the coming weekend if I have enough time. </p> http://bbpress.org/forums/topic/advanced-search-options-where-and-what-are-they#post-21219 Tue, 02 Dec 2008 20:11:47 +0000 chrishajer 21219@http://bbpress.org/forums/ <p>In defense of bbPress, I think they went without search for a long time <strong>intentionally</strong>. It was not even included in the stock template until recently. The intention was that you could use tags to find specific content, click a member's name to get all the replies they made and topics they started, etc.</p> <p>For topics with no replies, there is a default view for that (look at the home page of this forum, near the bottom, to see it.) For other views, you probably want the My Views plugin by _ck_.</p> <p><a href="http://bbpress.org/plugins/topic/my-views/" rel="nofollow">http://bbpress.org/plugins/topic/my-views/</a><br /> <a href="http://bbshowcase.org/forums/" rel="nofollow">http://bbshowcase.org/forums/</a><br /> (look at the bottom right for all the views) </p> http://bbpress.org/forums/topic/advanced-search-options-where-and-what-are-they#post-21209 Tue, 02 Dec 2008 19:01:44 +0000 block 21209@http://bbpress.org/forums/ <p>Interesting. There any code from there you can release in the mean time? Pretty disappointed that bbPress has such poor search, but not surprising given how little information and documentation there is. </p> http://bbpress.org/forums/topic/advanced-search-options-where-and-what-are-they#post-21194 Tue, 02 Dec 2008 10:24:59 +0000 _ck_ 21194@http://bbpress.org/forums/ <p>bbPress's search is horrible (also in WordPress).<br /> There essentially is no advanced search at all.</p> <p>I have a Super-Search plugin that I've never finished and may go back one day to look at it when I have some time.</p> <p><a href="http://img183.imageshack.us/img183/7600/supersearchwl4.png" rel="nofollow">http://img183.imageshack.us/img183/7600/supersearchwl4.png</a> </p> http://bbpress.org/forums/topic/advanced-search-options-where-and-what-are-they#post-21187 Mon, 01 Dec 2008 22:57:55 +0000 block 21187@http://bbpress.org/forums/ <p>Hey there, I've been poring over things looking for documentation on any "advanced" search options, like searching by user, showing only threads with replies, etc. Pretty much any supported options outside the stock text string search. (Dreaded vBulletin example here: <a href="http://www.vbulletin.com/forum/search.php" rel="nofollow">http://www.vbulletin.com/forum/search.php</a>)</p> <p>Haven't really found very much so far, except that it's supposedly something that's only theme-enabled. Can anyone point me in the right direction for docs, examples, or anything else? </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19437 Thu, 25 Sep 2008 01:05:42 +0000 sambauers 19437@http://bbpress.org/forums/ <p>We aren't including Snoopy, only WP_Http (which is only around 24k of code if you discount the comments)</p> <p>The cron job is triggered by pageviews and then only if there are jobs to run.</p> <p>So on a visit to the site, the cron job list is checked (just an option in the meta table) then if there is something there to do, bbPress sends an HTTP request to itself, which the user doesn't need to wait for. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19426 Wed, 24 Sep 2008 18:06:32 +0000 _ck_ 19426@http://bbpress.org/forums/ <p>Ah, now I remember when they changed/fixed that in WordPress. Because my logs became full of the cron entries with WordPress pinging back to itself through the server. I think it polls like every few minutes too which was annoying. Hopefully that's changed too or it will have to be deleted asap (along with xmlrpc).</p> <p>1.0 is going to be so much more bloated over 0.9<br /> Snoopy/http class alone is 40k. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19421 Wed, 24 Sep 2008 16:31:46 +0000 mark8barnes 19421@http://bbpress.org/forums/ <p>The blog says, "To enable cron I’ve included the very new WP_Http class in BackPress." This class is documented here: <a href="http://codex.wordpress.org/Version_2.7#HTTP_API" rel="nofollow">http://codex.wordpress.org/Version_2.7#HTTP_API</a> which I read as providing exactly what you suggested - URL fetching to do a pseudo multi-task. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19417 Wed, 24 Sep 2008 13:13:17 +0000 _ck_ 19417@http://bbpress.org/forums/ <p>Just remember that the cron in bbPress/WordPress is fake and not parallel tasking. Meaning that the user waits while the task is completed when the task is triggered. Unless they are using url fetching via php to do a pseudo multi-task, which I doubt.</p> <p>Sometimes (most times) a real cron is better. I asked them way-back-when cron was first introduced in WordPress to allow a real cron job to be substituted but still work with the API and I don't believe they've ever done that. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19413 Wed, 24 Sep 2008 12:57:32 +0000 mark8barnes 19413@http://bbpress.org/forums/ <p>@_ck_ </p> <p>Whilst "technically" there may be thousands of deleted/spam topics, most administrators of large databases would regularly prune them. (I'm writing a plugin to solve that, now cron is in 1.0alpha 2 - should be released in October.) </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19379 Mon, 22 Sep 2008 13:47:20 +0000 _ck_ 19379@http://bbpress.org/forums/ <p>Off the top of my head this looks like a very bad idea performance-wise:</p> <pre><code>AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics AS t WHERE t.topic_status &lt;&gt; &#39;0&#39;)</code></pre> <p>Though mysql may optimize it on the fly. Technically there could be thousands of deleted topics on an active site which makes quite a few items to search for (though in your case, there are NO deleted topics, yet, so this won't be problem early on). However I think topic_status has an index? If not, it's even worse. </p> <p>Alternatively you could "over-search" the results (ie. ask for 100 instead of 30) and reverse the check of the 100 topic_id's to make sure topic_status=0.</p> <p>Since you'd end up using two queries, you could merge the two needs, to check titles AND to check topic status for the previously returned list of posts.</p> <p>You're dead right about pagination becoming insanely complicated in this case. This could be solved by limiting result lengths to just 100 items max, and then use my "over-query" idea to ask for 200 just incase it hits alot of deletions. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19375 Mon, 22 Sep 2008 11:29:14 +0000 mark8barnes 19375@http://bbpress.org/forums/ <p>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. </p> <pre><code>SELECT p . * , MATCH (p.post_text) AGAINST (&#39;test&#39;) AS search_score FROM bb_posts AS p WHERE MATCH (p.post_text) AGAINST (&#39;test&#39;) AND p.post_status = &#39;0&#39; AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics AS t WHERE t.topic_status &lt;&gt; &#39;0&#39;) ORDER BY search_score DESC LIMIT 30;</code></pre> <p>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. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19374 Mon, 22 Sep 2008 10:42:07 +0000 mark8barnes 19374@http://bbpress.org/forums/ <p>@sambauers: Let me just give you a couple of observations. Firstly, regarding the first search performed.</p> <p><code>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 &#39;%test%&#39; AND p.post_status = &#39;0&#39; AND t.topic_status = &#39;0&#39; GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;</code></p> <p>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:</p> <p><code>SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p WHERE p.post_text LIKE &#39;%test%&#39; AND p.post_status = &#39;0&#39; AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics AS t WHERE t.topic_status &lt;&gt; &#39;0&#39;) GROUP BY p.topic_id ORDER BY p.post_time DESC LIMIT 5</code></p> <p>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.</p> <p>_ck_ is also right that ordering by post_id is quicker than by post_time. 17% quicker in my case. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19307 Thu, 18 Sep 2008 05:19:05 +0000 _ck_ 19307@http://bbpress.org/forums/ <p>Remember, the problem with MATCH AGAINST is it will not do partial words or words under 4 characters. </p> <p>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.</p> <p>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).</p> <p><code>SELECT * FROM bb_posts WHERE post_text LIKE &#39;%test%&#39; LIMIT 5 ORDER BY post_id DESC;</code></p> <p>and</p> <p><code>SELECT * FROM bb_posts WHERE post_text LIKE &#39;%test%&#39; AND REGEXP &#39;[[:&lt;:]]%test%[[:&gt;:]]&#39; LIMIT 5 ORDER BY post_id DESC;</code></p> <p>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. </p> <p>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). </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19304 Wed, 17 Sep 2008 22:40:57 +0000 mark8barnes 19304@http://bbpress.org/forums/ <p>@_ck_:</p> <p>I currently have:<br /> have_query_cache: YES<br /> query_cache_limit 1048576<br /> query_cache_min_res_unit 4096<br /> query_cache_size 16777216<br /> query_cache_type ON<br /> query_cache_wlock_invalidateOFF</p> <p>though I've got plenty of RAM to tweak this. I don't want to just throw RAM at poorly optimised queries though.</p> <p>The simple query you mentioned takes 2 seconds for me (uncached). MATCH AGAINST with a full-text index takes 0.3 seconds. Keeping a full-text index only increased the database size by 30% in my case. That's well worth while in the days where disk space is so cheap. The regular expression executes even quicker (0.2s), but as you say does not do partial words.</p> <p>There's no reason why the core can't have an option for full-text in the settings page, with two different search methods depending on what is chosen. It would only take 20 or so lines of code. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19299 Wed, 17 Sep 2008 14:04:14 +0000 _ck_ 19299@http://bbpress.org/forums/ <p>Oh and looking at a MySQL performance guide for text search, apparently bbPress's search is using ALL of the slowdown triggers:</p> <p>order by<br /> group by<br /> and worst of all SQL_CALC_FOUND_ROWS</p> <p>How long does this query take without the modifiers:<br /> <pre><code>SELECT * FROM bb_posts WHERE post_text LIKE &#39;%test%&#39; LIMIT 5;</code></pre> <p>because that's as fast as it can possibly get without an index and should be used as a baseline.</p> <p>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.</p> <p>You might be able to just use LIKE with a simple sort by post date DESC.</p> <p>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. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19298 Wed, 17 Sep 2008 13:53:52 +0000 _ck_ 19298@http://bbpress.org/forums/ <p>But keeping a full-text index can get rather huge, which is probably why bbPress doesn't use it by default. </p> <p>BTW, MySQL 5.1 apparently has some nice improvements in fulltext search.</p> <p>If you have your own dedicated or vps server you can tinker with some mysql settings to speed things up. (You never did answer if you have mysql cache turned on) </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19295 Wed, 17 Sep 2008 11:25:10 +0000 mark8barnes 19295@http://bbpress.org/forums/ <p>@sambauers:</p> <p>It seems to me that the most important thing that you can do very easily is to actually utilise the full-text index by using MATCH AGAINST rather than LIKE %…%. That's an easy win. This also makes relevancy searching possible.</p> <p>The next thing would be to ensure that post_status is updated when topic_status is changed. That way, you could ignore topic_status when searching posts, and lose the join. </p> http://bbpress.org/forums/topic/searching-forums-for-my-past-posts#post-19290 Wed, 17 Sep 2008 02:12:46 +0000 RossB 19290@http://bbpress.org/forums/ <p>Thanks, Chris and _ck_. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19277 Tue, 16 Sep 2008 15:01:33 +0000 _ck_ 19277@http://bbpress.org/forums/ <p>Super-Search is currently over 20k and only about halfway done in what it needs to do. All the bbPress core needs is a proper way to and/or words and disable partial matching instead of defaulting to just searching for every single fraction of each word. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19274 Tue, 16 Sep 2008 14:26:08 +0000 sambauers 19274@http://bbpress.org/forums/ <p>@_ck_</p> <p>If you want we can work together on improving the existing search to make it better. We should be offering a "super" search in core. </p> http://bbpress.org/forums/topic/searching-forums-for-my-past-posts#post-19267 Tue, 16 Sep 2008 04:23:11 +0000 _ck_ 19267@http://bbpress.org/forums/ <p>You can also do it this way:<br /> <a href="http://google.com/search?q=RossB+inurl%3Abbpress+org+forums+site%3Abbpress.org&#38;num=100&#38;filter=0" rel="nofollow">http://google.com/search?q=RossB+inurl%3Abbpress+org+forums+site%3Abbpress.org&#38;num=100&#38;filter=0</a> </p> http://bbpress.org/forums/topic/searching-forums-for-my-past-posts#post-19265 Tue, 16 Sep 2008 02:33:31 +0000 chrishajer 19265@http://bbpress.org/forums/ <p>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. </p> http://bbpress.org/forums/topic/searching-forums-for-my-past-posts#post-19263 Tue, 16 Sep 2008 02:23:16 +0000 RossB 19263@http://bbpress.org/forums/ <p>Just wondering if there's any way to search the forum via my user name. I would like to locate all past posts and the great advice I received, and collate it. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19256 Mon, 15 Sep 2008 23:53:54 +0000 _ck_ 19256@http://bbpress.org/forums/ <p>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).</p> <p>I assume you are using the bb-benchmark plugin to watch those queries happen (if not, you should be).</p> <p>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 <code>my.cnf</code> look like? (do a <code>cat /etc/my.cnf</code> in your shell)</p> <p>Try going into phpmyadmin (or command line) and test that first test query against adding <code>AND post_text REGEXP &#39;[[:&lt;:]]%test%[[:&gt;:]]&#39;</code> to the query like so:</p> <p><code>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 &#39;%test%&#39; AND p.post_text REGEXP &#39;[[:&lt;:]]%test%[[:&gt;:]]&#39; AND p.post_status = &#39;0&#39; AND t.topic_status = &#39;0&#39; GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;</code></p> <p>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 <a href="http://sphinxsearch.com" rel="nofollow">http://sphinxsearch.com</a> 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: <a href="http://svn.scriblio.net/plugin/trunk/scriblio.php" rel="nofollow">http://svn.scriblio.net/plugin/trunk/scriblio.php</a> </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19255 Mon, 15 Sep 2008 23:11:14 +0000 mark8barnes 19255@http://bbpress.org/forums/ <p>In other words, bbpress is not using MATCH … AGAINST at all, it's using the horribly slow LIKE %…%</p> <p>You couldn't tell me the hook for manipulating the search facility, could you? </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19248 Mon, 15 Sep 2008 21:32:04 +0000 mark8barnes 19248@http://bbpress.org/forums/ <p>I tested this on a very large database under very low load. The standard bbpress search creates two queries, namely:</p> <p><code>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 &#39;%test%&#39; AND p.post_status = &#39;0&#39; AND t.topic_status = &#39;0&#39; GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;</code></p> <p>and</p> <p><code>SELECT t.*, MIN(p.post_id) as post_id, GROUP_CONCAT(p.post_text SEPARATOR &#39; &#39;) 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 = &#39;0&#39; AND p.post_status = &#39;0&#39; AND ( (t.topic_title LIKE &#39;%test%&#39;) OR p.post_text LIKE &#39;%test%&#39; ) GROUP BY t.topic_id ORDER BY t.topic_time DESC LIMIT 30</code></p> <p>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). </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19200 Sun, 14 Sep 2008 08:41:57 +0000 _ck_ 19200@http://bbpress.org/forums/ <p>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.</p> <p>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. </p> <p>Query example from my Super-Search plugin:<br /> <pre><code>WHERE post_text LIKE &#39;%&quot;.$term.&quot;%&#39;&quot; AND post_text REGEXP &#39;[[:&lt;:]]&quot;.$term.&quot;[[:&gt;:]][^\&#39;]&#39; &quot;</code></pre> <p>Compare the performance of that against a fulltext search that uses <code>&quot;MATCH post_text AGAINST $term&quot;</code> 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.</p> <p>The only downside to the trick is you cannot do partial word searches that way. ie. <code>$term=&quot;cat&quot;</code> will only return posts with the exact word "cat" and not "cats" or "category". But it should be way faster.</p> <p>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.</p> <p>Many large sites end up using <a href="http://sphinxsearch.com/features.html">sphinxsearch</a> to replace fulltext search. You could interface it to bbPress via their PHP api.</p> <p>You can read more workarounds on the mysql fulltext search page (with far more knowledgeable people than me) <a href="http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html</a> </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19195 Sat, 13 Sep 2008 16:58:16 +0000 mark8barnes 19195@http://bbpress.org/forums/ <p>On a related note, I realised that my forum home page was taking a long time to load. My queries log showed that getting the recent posts query was taking 3 seconds to run. (Naturally enough if you have tens of thousands of topics.) An index on topic_sticky on {$bbdp-&gt;prefix}topics solved that problem.</p> <p>Even worse, going into the admin pages would take anything upto 100 seconds, I think just displaying the "Recently Moderated" panel. An index on post_status on {$bbdp-&gt;prefix}posts was needed there.</p> <p>Perhaps the devs should think about those two indexes as standard?</p> <p>My fulltext problem remains, however. </p> http://bbpress.org/forums/topic/fulltext-searching-very-slow#post-19193 Sat, 13 Sep 2008 16:02:04 +0000 mark8barnes 19193@http://bbpress.org/forums/ <p>I have a large forum, and full-text searching is very slow. Looking at the slow queries log, 30-45 seconds seems about 'normal'. Any suggestions about what could be done? tina.nl (which is bigger than mine has an almost instant search response) </p>