• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Rob Spoor
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Holloway
  • Piet Souris
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Himai Minh

jforum_search_wordmatch

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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]
 
Ew. You guys are ugly with a capital UG. Here, maybe this tiny ad can help:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic