UBB.Developers Forums General Discussion Suggestions and Feedback Searching...Wow, Fast

I'm totally knocked out by how fast "all forum" searching is in this version.

Has the search engine been speeded up, or are you guys indexing each posting? If the latter, is that functionality a hack or will it be in the release version?

thanks,

JIM

Everything you see here is base code, we won't begin modifying until after release

Well...I'm just...stupefied!

It's not just eons faster than the search function a year or so ago, but it's faster than conventional wisdom has told me mySQL can be searched. I'd been told by tech people that only indexing can achieve decent speed re: widespread mySQL searches. Whadja do, Rick? It's like a miracle!

I'm about to switch over my site's discussion boards (currently 1000 postings/day, and a backlog of about 600K) FINALLY to ubbthreads (after hanging around this program for a couple of years, monitoring its progress and helping on some copy issues). If I can get away with NOT indexing all that data, it'd sure save me disk space and bandwidth...


JIM LEFF

...or, wait. You didn't actually answer this.

is this forum indexed?

Would the zlib compression be speeding it up?

Compression only speeds up the rendering of results (displaying the html that shows the results) and not the actual searches themselves

I haven't indexed anything other than what's in the base code. I'll check it out and get back to you unless someone else beats me to it

No indexing has been done on the body of the posts. It is a bit quicker than before because on the results page there are less queries to display the results. There used to be an extra query per result, which would make the total queries about 30 for a 25 result page. I've made a couple changes to db schema and used a join to cut out the extra query.

Scream, whatever you did, it helped a lot!

I'm about to import 300,000 back postings into ubbthreads. I suppose I won't get the same speed in "all forums" searching. Is indexing practical/helpful in your opinion? Is it supported, or will we need to create a from-scratch hack?

I know there was some discussion about doing full text indexes before on the body. Honestly, I'm not real sure if they are efficient or what the tradeoff is if it adds any speed. I'll need to differ to anyone else that might know more on this one.

Are you refering to indexing the text of the B_Body field? Here is some info that may help:

<snip>
In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from VARCHAR and TEXT columns. Indexing always happens over the entire column and partial indexing is not supported. See section 6.8 MySQL Full-text Search for details.
</snip>

Related link:
http://www.mysql.com/doc/I/n/Indexes.html

I think it would speed things up. But, one would have to be using a database that supports it.

I had noticed the FULLTEXT index feature before, but hadn't got around to reading the full description of it until now.

It does sound like it would speed up searches.

Do you think the relevance/weighting aspect would be desirable for forum searches?

I don't really know what kind of a performance increase this will offer. The way I use my site is not quite the same as most as I mainly use the forum software to register users. I think Max Fisch had some info on this as he had done it at his forums. Here's a post he made:

https://ubbdev.com/threads/php/...amp;Main=44341&Search=true#Post44341

Hiah... yes, I have fulltext indexing on the body of my posts. This resulted in very significant performance enhancement when using "OR" searches in 5.5 ... the "OR" search was blazingly fast, and I made it the default on the search page at the time.

However, as I just upgraded to 6.02, I'm noticing that the fully indexed "OR" search seems to be significantly slower than the stock "entire phrase" search (which can't take advantage of the indexing anyway). I haven't done much testing yet though. But at first glance, I don't think it's really worth it for most users to do a fulltext index right now.

But I'm not running mysql 4.01 , so if you are, you might want to give it a shot... 4.01 can take advantage of fulltext indexing with "AND" searches as well.

Regards,
Max