aspose file tools*
The moose likes JForum and the fly likes jforum_search_wordmatch Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » JForum
Bookmark "jforum_search_wordmatch" Watch "jforum_search_wordmatch" New topic
Author

jforum_search_wordmatch

Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
We're beginning to see a lot of slow queries with this table.
It has about 28 million rows in it. It appears it stores word-post_id-title info. Is there a way to purge the table or optimize the feature?
[originally posted on jforum.net by kkim]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
In the CVS Repository there is a tools/serachIndexer section that contains a command line Java class for rebuilding your index tables. This might help cut down the table.

As to optimization, a lot depends on your SQL server's use of indices but in general, make sure that:

The word_id column in the jforum_search_wordmatch table is defined as a foreign key connected to the word_id column in the jforum_search_words table. (Some DB's Servers may not support this).

Make sure that there are indices defined for all the individual columns in both the jforum_search* tables.

There is also a search.word.matching config parameter that can be set to "like" or "equals". The "like" value creates a query using a "LIKE" clause that can be a performance issue.

Also, you may want to add to the config search.exclude.words list to cut back on the noise words.


[originally posted on jforum.net by monroe]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Thanks for the quick response.

I'm a sys admin/dba and we're exploring ways to spare some of the load from the database server. It looks like when search indexing is enabled, it generates a heavy load ofselects/inserts to the search tables on each post operation. With it disabled, the savings are drastic.

My question is -

Does it effectively disable the search feature if we set

search.indexing.enabled = false

or is there an alternative? It seems like there's an option to run the indexer as a scheduled background task but I can't tell if that can't be activated with the above option off.

Kyong
[originally posted on jforum.net by kkim]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Ah, yes, the indexing part of the search engine will parse each post into words, then do a search for existing words in the words table. But I think most DB implementations try to do this via an "in clause". However, each new word in a post will generate an insert into the words table and each word in a post will generate an insert into the words to post table.

From what I can tell from the code, to change from indexing at post time to indexing on a schedule, you need to change the search.indexer.implementation to the following:

search.indexer.implementation = ${search.indexer.quartz}

This will cause the index job to be run via the Quartz job system. The schedule will be based on the indexer cron job expression in the quartz-jforum.properties file.

You might need to find the quartz package docs to figure out how to define the timing.
[originally posted on jforum.net by monroe]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Just a note: the search module was completely rewriten, and will be released with JForum 2.1.8. There are no more search tables, as we're using Lucene.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: jforum_search_wordmatch