• Gil
  • Member
  • Offline
  • From: France
  • Registered: 2008-05-10
  • Posts: 42

Topic: 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.org/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,

Gil's Website

  • elbekko
  • FluxBB.org Developer
  • Online
  • From: Leuven, Belgium
  • Registered: 2008-04-30
  • Posts: 821

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

elbekko's Website

  • Smartys
  • Former FluxBB.org Developer
  • Offline
  • Registered: 2008-04-27
  • Posts: 2,095

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

Smartys's Website

  • Reines
  • FluxBB.org Developer
  • Offline
  • From: Scotland
  • Registered: 2008-05-11
  • Posts: 775

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).

Reines's Website

  • Gil
  • Member
  • Offline
  • From: France
  • Registered: 2008-05-10
  • Posts: 42

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)

Gil's Website

  • Gil
  • Member
  • Offline
  • From: France
  • Registered: 2008-05-10
  • Posts: 42

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.org/files.php?pid=274) already compatible with FluxBB 1.2.20? Is someone using the extension?

Gil's Website

  • Registered: 2008-09-21
  • Posts: 10

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.org/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 !

  • Smartys
  • Former FluxBB.org Developer
  • Offline
  • Registered: 2008-04-27
  • Posts: 2,095

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

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

Smartys's Website

  • Registered: 2008-09-21
  • Posts: 10

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.

  • elbekko
  • FluxBB.org Developer
  • Online
  • From: Leuven, Belgium
  • Registered: 2008-04-30
  • Posts: 821

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

elbekko's Website

  • Smartys
  • Former FluxBB.org Developer
  • Offline
  • Registered: 2008-04-27
  • Posts: 2,095

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.

Smartys's Website

  • Connor
  • FluxBB.org Developer
  • Offline
  • Registered: 2008-04-27
  • Posts: 1,023

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.

  • Registered: 2008-09-21
  • Posts: 10

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)

  • Connor
  • FluxBB.org Developer
  • Offline
  • Registered: 2008-04-27
  • Posts: 1,023

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

It would be quite easy for someone to add

15 Reply by Gil

  • Gil
  • Member
  • Offline
  • From: France
  • Registered: 2008-05-10
  • Posts: 42

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!

Gil's Website

  • Registered: 2008-09-21
  • Posts: 10

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.

17 Reply by Gil

  • Gil
  • Member
  • Offline
  • From: France
  • Registered: 2008-05-10
  • Posts: 42

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 ')

Gil's Website

  • Registered: 2008-09-21
  • Posts: 10

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)

  • Reines
  • FluxBB.org Developer
  • Offline
  • From: Scotland
  • Registered: 2008-05-11
  • Posts: 775

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

Reines's Website

  • Registered: 2008-09-21
  • Posts: 10

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" .