1 Topic by spikeweb (edited by spikeweb 2006-10-15 23:12)

  • Registered: 2005-11-26
  • Posts: 13

Topic: Mysql fulltext search mod question

Hello there,
first let me say that I am almost a newbee with MySQL stuff but I try to optimize my forum performances from a few days as they went down recently.
I run the latest release of Pun with  2180 users, 3709 topics and 45178 posts (as of today), provider is 1and1.fr
I have a slow "first page load" response time and in general the forum response time is poor so and am trying to check with the provider if this can come from their SQL server or not. If not, could it be on my side as the DB is already some 70 Mb large ?

I installed the MySQL fulltext search mod yesterday night to try to unload the server from the original search capability and I have now a question (remember I am a newbee on the sql side !!) :
if using this mod, shall I still need to use the pun_search_xxxx tables ? If no, I could then remove the original search index tables to lower the DB size ?

If the DB index is too large (as mine), could it be any influence on the index page load time ?

In general, what could be done to improve performance knowing that I cannot manage the server myself ? (just as an info, I read the all forum yesterday and picked up a few things, I also rebuilt the search index)

Thanks for your responses

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Mysql fulltext search mod question

If no, I could then remove the original search index tables to lower the DB size ?

Yes, you can remove the search_matches and search_words tables (search_cache is usually small and I'm not sure if the mod uses it)

If the DB index is too large (as mine), could it be any influence on the index page load time ?

Your DB isn't overly large, the issue is more likely your host (although they might deny it). If you're really curious and don't mind other people seeing the database queries for a little bit, you can enable PUN_DEBUG and PUN_SHOW_QUERIES and see which queries are actually slowing things down.

  • Registered: 2005-11-26
  • Posts: 13

Re: Mysql fulltext search mod question

Thanks for this answer.
To remove the index tables, do I have to modify any line of code as if I don't I presume that Pun will try to update the tables as it does not know that I use a different search method (only search.php is modified with the mod) ?? Is my comprehension true ? Or shall I have to empty the tables and that's it ? Or to dump them and delete them ?

I will use the DEBUG mode, you are right smile and post feedback here.

Thanks again

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Mysql fulltext search mod question

I think the mod properly disables the use of the regular search indexing (assuming you edit post.php, edit.php, etc). So I would simply delete all rows from the table

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Mysql fulltext search mod question

Jannson wrote:

##                    Also note that the search_* tables are not required
##                    after install and can therefor be dropped. The install
##                    does not remove these tables, but the mod makes PunBB
##                    to stop updating them. You can keep updating them by not
##                    replacing search_idx.php, in case you just want to try
##                    out the mod and have the ability to change back quickly.

  • Registered: 2005-11-26
  • Posts: 13

Re: Mysql fulltext search mod question

Hummm, my apologizes for not having read until the end ...
Anyway, thanks again for your help smile

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Mysql fulltext search mod question

spikeweb wrote:

Hummm, my apologizes for not having read until the end ...
Anyway, thanks again for your help smile

aha, ty elbekko smile

  • Registered: 2005-11-26
  • Posts: 13

Re: Mysql fulltext search mod question

Some feedback as it could help later on ...
I finally solved my problem by :
- switching to fulltext mod (this disable the native search and you don't need the original search_xx tables
- upgraded my DB server to MySQL5 instead of MySQL 4.0.xx, my provider allows to upgrade by switchingthe server
- I had to export the DB with mysqldumper to get file size below the 2 Mb limit of phpmyadmin and import it with phpmyadmin specifying file character set 'latin1'
- then switching the DB server config in config.php
that's  it and I have now a DB size of 39 Mb instead of 70 Mb wink

Thanks for your help !!

  • tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Re: Mysql fulltext search mod question

Very useful mod, but I have problem with polish letters like ? ? ? ?. Words with this letters aren't find sad

What should I do ?

Darmowe forum - Polish free forum hosting

tomekf's Website

10 Reply by tomekf

  • tomekf
  • Senior Member
  • Offline
  • From: Poland
  • Registered: 2005-10-30
  • Posts: 104

Re: Mysql fulltext search mod question

Any help?

Darmowe forum - Polish free forum hosting

tomekf's Website