Forums

Unfortunately no one can be told what FluxBB is - you have to see it for yourself.

You are not logged in.

#1 2008-05-21 07:44:11

Gil
Member
From: France
Registered: 2008-05-10
Posts: 175

Mysql DB size, "search_matches" table, and fulltext indexing

As I am approaching the limit of my mysql DB (authorised by my provider : 25Mo), I would like to know if it is possible to reduce the size.

The "search_matches" table size is greater than half the total DB size, and I saw that with the MySQL fulltext Search extension (http://www.punres.net/desc.php?pid=274) , "search_matches" and "search_words" tables can be suppressed:

Rickard wrote:

However, it is my intention to add support for MySQL fulltext indexing in PunBB 1.3. With that enabled, you will be able to delete that table. With fulltext indexing, MySQL maintains those index tables internally

(here: http://punbb.informer.com/forums/viewto … 944#p71944).

With fulltext search extension, what are the consequences on the DB size? OK, important  reduction as the biggest table is suppressed, but does the "ADD FULLTEXT" option on "posts" and "topics" tables increase the size of these tables? How much? If there is a significant increase, what is the final result, a gain or a loss of size?

Thanks,

Offline

#2 2008-05-21 07:48:30

elbekko
Former Developer
From: Leuven, Belgium
Registered: 2008-04-30
Posts: 1,132
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

I think the size will remain about the same, perhaps a bit less.


Ben
SVN repository for my extensions - The thread
Quickmarks 0.5
“Question: How does a large software project get to be one year late? Answer: One day at a time!” - Fred Brooks

Offline

#3 2008-05-21 11:29:40

Smartys
Former Developer
Registered: 2008-04-27
Posts: 3,139
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

I think it would be a bit less, but I don't think people have measured. In any case, the size is at least partly shifted from the search tables to the posts table.
Personally, I wouldn't host with a company that limits my databases to 25 MB. tongue

Offline

#4 2008-05-21 11:38:57

Reines
Administrator
From: Scotland
Registered: 2008-05-11
Posts: 3,197
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

lol the database for my old site is 493mb :s (no longer running/hosted though).

Offline

#5 2008-05-21 12:28:52

Gil
Member
From: France
Registered: 2008-05-10
Posts: 175

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Smartys wrote:

I think it would be a bit less, but I don't think people have measured. In any case, the size is at least partly shifted from the search tables to the posts table.

I think so, I will not try to add the extension.
Current actions is to do some cleaning, et to add stopwords in my french language pack (many words are missing in my old stopwords list).

Personally, I wouldn't host with a company that limits my databases to 25 MB. tongue

Database size is not the only criteria to use to compare hosts; and at the beginning I wanted only a very small forum.
For the 25Mb, it's not exactly a limit, I can change the contract, or I can pay to have bigger database smile Compared to other hostings (*** at least here, in France ***) I think the price is more than correct for the set of services (1,20 € PM for one site and 2,40 € PM for the second, respectively 600Mo and 10Go), but to change DB size (from 25 to 100Mo), the price of the first hosting is multiplied by 5...

Last edited by Gil (2008-09-22 20:36:51)

Offline

#6 2008-09-22 22:05:07

Gil
Member
From: France
Registered: 2008-05-10
Posts: 175

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Gil wrote:

With fulltext search extension, what are the consequences on the DB size? OK, important  reduction as the biggest table is suppressed, but does the "ADD FULLTEXT" option on "posts" and "topics" tables increase the size of these tables? How much? If there is a significant increase, what is the final result, a gain or a loss of size?
Thanks,

Linked to http://fluxbb.org/forums/topic/1867/database-size/, and thank to oli_v_ier, the answer seems to be that afthe final size is about half the initial size.

Now: except the update of $punbb_versions in install_mod.php, is punbb fulltext search extension (http://www.punres.net/files.php?pid=274) already compatible with FluxBB 1.2.20? Is someone using the extension?

Offline

#7 2008-09-23 08:09:15

oli_v_ier
Member
Registered: 2008-09-21
Posts: 47

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Gil wrote:

Now: except the update of $punbb_versions in install_mod.php, is punbb fulltext search extension (http://www.punres.net/files.php?pid=274) already compatible with FluxBB 1.2.20? Is someone using the extension?

I use the extension, but not yet on FluxBB.

This mod has one main disadvantage: the results are given for each keyword (OR instead of AND).
Example: search for "yellow submarine" will return each topics (or messages) about "yellow" or "submarine", not both together !

Offline

#8 2008-09-23 17:07:57

Smartys
Former Developer
Registered: 2008-04-27
Posts: 3,139
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

I believe you can use boolean operators to specify AND as opposed to OR.

Offline

#9 2008-09-23 19:15:39

oli_v_ier
Member
Registered: 2008-09-21
Posts: 47

Re: Mysql DB size, "search_matches" table, and fulltext indexing

If you mean using boolean operators with the keywords, nope: doesn't work, I've tried -> same results as without using them.

Example: search "Yellow AND submarine" will give some topics with only "yellow" in it and somes topics with only "submarine" in it. The topics with both "yellow" and "submarine" doesn't come in the top of the search results.

Offline

#10 2008-09-23 19:16:44

elbekko
Former Developer
From: Leuven, Belgium
Registered: 2008-04-30
Posts: 1,132
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

What about "Yellow + submarine"?


Ben
SVN repository for my extensions - The thread
Quickmarks 0.5
“Question: How does a large software project get to be one year late? Answer: One day at a time!” - Fred Brooks

Offline

#11 2008-09-23 20:51:31

Smartys
Former Developer
Registered: 2008-04-27
Posts: 3,139
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Or "+Yellow +submarine" (without the "s). Or, if you want the exact phrase, just use quotes and no + operator.

Offline

#12 2008-09-23 21:16:01

Connor
Former Developer
Registered: 2008-04-27
Posts: 1,127

Re: Mysql DB size, "search_matches" table, and fulltext indexing

I can't see in boolean mode in that mod so you can't use boolean operators afaik.

Offline

#13 2008-09-24 03:20:37

oli_v_ier
Member
Registered: 2008-09-21
Posts: 47

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Connor wrote:

I can't see in boolean mode in that mod so you can't use boolean operators afaik.

Would it be lot of work to add ?

By the way, this mod works with FluxBB 1.2.20 (I've just done the transition from punBB 1.16 tongue ), but the file search.php has to be "customized"...
Here's my file search.php:  search.php (5kB) .

If somebody finds some security problem with this file, thank you to tell me.

Last edited by oli_v_ier (2008-09-29 20:56:26)

Offline

#14 2008-09-24 07:32:56

Connor
Former Developer
Registered: 2008-04-27
Posts: 1,127

Re: Mysql DB size, "search_matches" table, and fulltext indexing

It would be quite easy for someone to add

Offline

#15 2008-09-29 19:53:00

Gil
Member
From: France
Registered: 2008-05-10
Posts: 175

Re: Mysql DB size, "search_matches" table, and fulltext indexing

oli_v_ier wrote:

By the way, this mod works with FluxBB 1.2.20 (I've just done the transition from punBB 1.16 tongue ), but the file search.php has to be "customized"...
Here's my file search.php: search.zip (5kB) .

If somebody finds some security problem with this file, thank you to tell me.

(Not found ?!)
Is your customization concerning rights?
Because, here on the punres mod topic, someone was saying:

The permissions problem still seems to be there.
Even after making the modification suggested above, a guest can still search right into forum posts (when you choose 'show results as posts') where neither guests nor members should have the privileges to be able to see anything.

This can be very annoying!

Offline

#16 2008-09-29 20:55:49

oli_v_ier
Member
Registered: 2008-09-21
Posts: 47

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Gil wrote:

Because, here on the punres mod topic, someone was saying:

The permissions problem still seems to be there.
Even after making the modification suggested above, a guest can still search right into forum posts (when you choose 'show results as posts') where neither guests nor members should have the privileges to be able to see anything.

This can be very annoying!

Nope, no problem with this.
I have forums that only Administrators can see. When guests or members make a search, even with 'show results as posts' , they don't have access to these forums.

Note: I've updated my search.php file since the last time.

Offline

#17 2008-10-07 19:27:07

Gil
Member
From: France
Registered: 2008-05-10
Posts: 175

Re: Mysql DB size, "search_matches" table, and fulltext indexing

oli_v_ier wrote:

Nope, no problem with this.
I have forums that only Administrators can see. When guests or members make a search, even with 'show results as posts' , they don't have access to these forums.

Thanks. Installed, it works.
Size is now 60% of the previous size, good!

Several disvantages, however:

  • no posible search on titles only

  • string is 4 characters minimum (shit, we were using a special keyword with only 3 characters lol)

  • several words are always combined with OR (it seems to). Nor '+' neither "AND' does appear to work (even with " or ')

Offline

#18 2008-10-07 19:46:46

oli_v_ier
Member
Registered: 2008-09-21
Posts: 47

Re: Mysql DB size, "search_matches" table, and fulltext indexing

Gil wrote:

Thanks. Installed, it works.

Cool smile .

Gil wrote:

Several disvantages, however:

  • no posible search on titles only

  • string is 4 characters minimum (shit, we were using a special keyword with only 3 characters lol)

  • several words are always combined with OR (it seems to). Nor '+' neither "AND' does appear to work (even with " or ')

Yep, the 2nd and 3rd are major disvantages.
I don't know if the second one can be fixed, but the 3rd one seems easy to correct as Connor said above: "It would be quite easy for someone to add". But I don't have enough knowledge of php to do this.

The code to change would be in these lines:

168 // Keyword search
169                $match_msg = 'MATCH (p.message) AGAINST (\''.$db->escape($keywords).'\')';
170                if($sort_by == 5)
171                    $sort_by_sql = 'score';
172
173                $select_sql = $match_msg.' AS score, ';
174                $match_sql = $match_msg;

search.php

Does anyone have a suggestion ?

Last edited by oli_v_ier (2008-10-07 19:47:36)

Offline

#19 2008-10-07 20:01:07

Reines
Administrator
From: Scotland
Registered: 2008-05-11
Posts: 3,197
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

oli_v_ier wrote:

Does anyone have a suggestion ?

Try having a look at the fulltext extension for 1.3: http://fluxbb.org/trac/browser/extensio … nifest.xml

Offline

#20 2008-12-02 13:49:15

oli_v_ier
Member
Registered: 2008-09-21
Posts: 47

Re: Mysql DB size, "search_matches" table, and fulltext indexing

oli_v_ier wrote:
Gil wrote:

Several disvantages, however:

  • no posible search on titles only

  • string is 4 characters minimum (shit, we were using a special keyword with only 3 characters lol)

  • several words are always combined with OR (it seems to). Nor '+' neither "AND' does appear to work (even with " or ')

Yep, the 2nd and 3rd are major disvantages.
I don't know if the second one can be fixed, but the 3rd one seems easy to correct as Connor said above: "It would be quite easy for someone to add". But I don't have enough knowledge of php to do this.

Done, thanks to meo, here's the modified files:
search.zip

Now boolean searches are possible on the fulltext search, you can use:  '+' , '-', quotes " " to search for a phrase, '*' as a wildcard.
There's also 2 more search options: order by "Thread lenght" , "Thread popularity" .

Offline

#21 2014-10-03 17:13:37

grognard
Member
From: UK
Registered: 2014-09-18
Posts: 66
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

NECRO THREAD YE BE WARNED

Erm chaps. I just installed FluxBB and with only 31 test posts on my forum so far my database size has already hit 0.5mb.

Erm.

Uh.

That seems to be growing quite fast compared to what I am used to with other forums?

Does any of the advice in this thread on limiting the database size still hold true for current 1.5 version of FluxBB?

I'm using a secure digital card for my server storage and would run in to problems with a huuuge db.

Last edited by grognard (2014-10-03 17:15:35)

Offline

#22 2014-10-04 11:11:59

Franz
Lead developer
From: Germany
Registered: 2008-05-13
Posts: 6,686
Website

Re: Mysql DB size, "search_matches" table, and fulltext indexing

The search table on this forum is about 27 MB in size. I don't think that's all that bad. Of course, the growth will probably decrease over time, as less and less new words will be posted in your threads...


fluxbb.de | develoPHP

"As code is more often read than written it's really important to write clean code."

Offline

Board footer

Powered by FluxBB