MySQL Full Text search

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • iardon
    Senior Member
    • Jun 2005
    • 291
    • 3.8.x

    MySQL Full Text search

    When is it necessary to switch to MySQL Full Text search? Or is that a personal preferece thing?

    From the manual..

    To get around the large amount of space these tables can occupy we implemented MySQL Fulltext Search.
    If MYSQL Fulltext Search keeps the database size down, why not have that as the default setting?
    DMCTalk.com forums - For DeLorean owners and enthusiasts.
  • feldon23
    Senior Member
    • Nov 2001
    • 11291
    • 3.7.x

    #2
    Because unless it's a dedicated server, you can't set MySQL fulltext to search for 3-letter words. You can't add or remove words from the list. There are other limitations I believe.

    Comment

    • Marco van Herwaarden
      Senior Member
      • Nov 2004
      • 6999
      • 3.8.x

      #3
      Depending on your MySQL version it is the default nowadays i think.

      MySQL Fulltext also uses it's own indexes however, so the space reduction could be minor.
      Want to take your board beyond the standard vBulletin features?
      Visit the official Member to Member support site for vBulletin Modifications: www.vbulletin.org

      Comment

      • feldon23
        Senior Member
        • Nov 2001
        • 11291
        • 3.7.x

        #4
        If you have no concerns about MySQL space usage, then it's a wash between the two. MySQL fulltext lets you search for phrases ("google pagerank").

        You can accomplish similar space savings by backing up the MySQL database, skipping the word table.

        Comment

        • Andy
          Senior Member
          • Jan 2002
          • 5886
          • 4.1.x

          #5
          Isn't there two types of Fulltext searching, boolean and natural language?

          The online manual does not mention which type of Fulltext searching it is referring too.

          Comment

          • feldon23
            Senior Member
            • Nov 2001
            • 11291
            • 3.7.x

            #6
            I see nothing on that page in the user manual which distinguishes between one or the other.

            I can tell you that Natural search is awful. The results are almost completely random. I prefer to do a Google search of forums that use Natural search.

            Comment

            • Andy
              Senior Member
              • Jan 2002
              • 5886
              • 4.1.x

              #7
              Originally posted by feldon23
              I see nothing on that page in the user manual which distinguishes between one or the other.

              I can tell you that Natural search is awful. The results are almost completely random. I prefer to do a Google search of forums that use Natural search.
              I agree natural language is worse then awful it's useless.

              I think the manual is talking about natural language:

              By default, vBulletin will use its internal indexing feature. The results of this indexing process is stored in two tables, word and postindex. This provides a fast search mechanism but can cause problems on larger forums due to the ever increasing size of these tables.
              The above statement is only true with Fulltext enabled using natural language.

              Comment

              • feldon23
                Senior Member
                • Nov 2001
                • 11291
                • 3.7.x

                #8
                By default, vBulletin will use its internal indexing feature. The results of this indexing process is stored in two tables, word and postindex. This provides a fast search mechanism but can cause problems on larger forums due to the ever increasing size of these tables.
                Not sure how you get MySQL natural out of that. Neither MySQL fulltext natural nor fulltext boolean use special tables. That quote is describing vBulletin's built-in search (which is the only way you could search in MySQL 3.22 and earlier).

                Comment

                • Wayne Luke
                  vBulletin Technical Support Lead
                  • Aug 2000
                  • 73976

                  #9
                  The following applies to the Fulltext Search Implementation.

                  Whether a user has access to Boolean or Natural Language is set by a usergroup option in the Usergroup Manager. The option if enabled allows users to choose a Boolean Search. Otherwise the search will default to Natural Language.

                  From the admin help entry:
                  Boolean Fulltext Search
                  This permission allows members of this group to search your forums using the Boolean option. This permission only applies to search performed when your forum's search type is set to Fulltext. This permission allows Boolean searches, which includes the usage of AND, OR, and NOT to limit results. This option can be server intensive for very large forums so its usage is cautioned. When this permission is disabled, the Fulltext search will use a less intensive relevance based search.

                  For this permission to work you must have the search engine enabled in your vBulletin options and the usergroup must also have search permission.
                  vBulletin does not support the full range of Boolean operaters listed in MySQL's Fulltext indexing documentation.
                  Translations provided by Google.

                  Wayne Luke
                  The Rabid Badger - a vBulletin Cloud demonstration site.
                  vBulletin 5 API

                  Comment

                  • iardon
                    Senior Member
                    • Jun 2005
                    • 291
                    • 3.8.x

                    #10
                    I have mysql version 5.0.27-standard, and my forum is running vbulletin search.

                    Originally posted by Marco van Herwaarden
                    Depending on your MySQL version it is the default nowadays i think.

                    My database size is just 110 meg. So, it's not a big deal and just leave it as it is?
                    DMCTalk.com forums - For DeLorean owners and enthusiasts.

                    Comment

                    • feldon23
                      Senior Member
                      • Nov 2001
                      • 11291
                      • 3.7.x

                      #11
                      I would estimate that 40MB of your database is dedicated to search.

                      Comment

                      • iardon
                        Senior Member
                        • Jun 2005
                        • 291
                        • 3.8.x

                        #12
                        Originally posted by feldon23
                        I would estimate that 40MB of your database is dedicated to search.
                        Ok, but it was mentioned earlier in this thread that moving to mysql full text might not save that much space.

                        I'm just trying to get a definitive answer as to what's the best option. I don't understand so well the technical aspect.

                        So like I mentioned, I'm using mysql 5.0.27-standard and with a database of around 110, should I enable full text?

                        Yay or Nay?
                        DMCTalk.com forums - For DeLorean owners and enthusiasts.

                        Comment

                        • feldon23
                          Senior Member
                          • Nov 2001
                          • 11291
                          • 3.7.x

                          #13
                          You have the most control over how your searching works with the vBulletin built-in search. Unless you have a specific reason to switch, you can stick with it.

                          Comment

                          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                          Working...