indexTable space utilization and maintenance?

[David Warton]

Hi. I'm new to this forum and JForum in general. I've searched for a forum topic similar to my question below, but couldn't find one. Please point me in the direction of the solution if this has been previously asked.

I'm a recently new hire as a database engineer in a software development company that has integrated JForum into their product offering. Reviewing some of our slow running application queries, many of them point to JForum. Which makes me question whether JForum is properly installed/configured and if there is any tweaking or maintenance that can be done to improve performance. Without any useful JForum documentation I'm resorting to posting my question(s) here.

We've integrated JForum into our Java/Grails application utilizing a MySQL database on the back end. In the past year on this installation, we've initiated 22 forums, that hosts 12,875 topics and a total of 66,460 posts. This doesn't seem to excessive to me. Our overall JForum database size is 11.3Gb of which the indexTable comprises approx. 11.2Gb ofindexTable space utilization and maintenance? that space (+99% of total used space). This table has more than 18 million rows which seems excessive based upon the overall size of the database and the number of posts. One of the simple queries that was identified as slow executing against the JForum database is "select name_ from indexTable where deleted_ = 1". I don't have any explanation as to why this query would be executed from within JForum or our application, but the slow query log this past week identified this query having been executed 19 times this past week for an average execution time of more than 125 seconds. The "deleted_" column is not indexed. I considered adding an index to it to improve this query, but thought I would check with others to determine if some other action should first be taken against this table or updating our installation configuration.

Thanks in advance for all responses and advice. Let me know if you need more information regarding our setup.

[David Warton]

.... Or to simplify things, can the rows in indexTable where deleted_ = 1 be deleted? This will reduce the number of rows in this table significantly.

[Jaikiran Pai]

David, welcome to CodeRanch!

That's the first time I'm hearing about a table named indexTable. Are you sure it's part of core JForum and not something your application might have added? What does its schema look like?

[David Warton]

Thanks for your response.

Finally heard back from our development team that this is a table that they've added in order for JForum to function properly within our environment.