jforum_search_wordmatch

[Migrated From Jforum.net]

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]

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]

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]

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]

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]