Exact phrase searching

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Scam Warning
Post Reply
User avatar
philwhite
Registered User
Posts: 122
Joined: Wed Aug 22, 2007 12:47 am
Contact:

Exact phrase searching

Post by philwhite » Mon Feb 11, 2008 6:05 pm

There are numerous queries on the board from folk including myself as to how to implement exact match searching under PHPBB3. No discussion gave a straight answer, so I'll try to put my experiences down here.

Exact match searching or searching for exact phrases can be implemented for PHPBB3 boards without using any modifications if you are using MySQL as your database.

There are two further requirements.
  • The database tables must use the myISAM storage engine (see below)
  • The MySQL database should be version 4.1.7 or later (more information can be found here).
Many providers will create tables with the innoDB engine by default when you first set up PHPBB3, and you may not be able to control this. You can, however, change the engine used after the tables are created. If you have access to phpMyAdmin or a similar database administration facility (in most cases, you will have access to phpMyAdmin if you are using MySQL), run the following SQL queries to change the engine type used:

Code: Select all

    ALTER TABLE phpbb_acl_groups ENGINE=MyISAM;
    ALTER TABLE phpbb_acl_options ENGINE=MyISAM;
    ALTER TABLE phpbb_acl_roles ENGINE=MyISAM;
    ALTER TABLE phpbb_acl_roles_data ENGINE=MyISAM;
    ALTER TABLE phpbb_acl_users ENGINE=MyISAM;
    ALTER TABLE phpbb_attachments ENGINE=MyISAM;
    ALTER TABLE phpbb_banlist ENGINE=MyISAM;
    ALTER TABLE phpbb_bbcodes ENGINE=MyISAM;
    ALTER TABLE phpbb_bookmarks ENGINE=MyISAM;
    ALTER TABLE phpbb_bots ENGINE=MyISAM;
    ALTER TABLE phpbb_config ENGINE=MyISAM;
    ALTER TABLE phpbb_confirm ENGINE=MyISAM;
    ALTER TABLE phpbb_disallow ENGINE=MyISAM;
    ALTER TABLE phpbb_drafts ENGINE=MyISAM;
    ALTER TABLE phpbb_extensions ENGINE=MyISAM;
    ALTER TABLE phpbb_extension_groups ENGINE=MyISAM;
    ALTER TABLE phpbb_forums ENGINE=MyISAM;
    ALTER TABLE phpbb_forums_access ENGINE=MyISAM;
    ALTER TABLE phpbb_forums_track ENGINE=MyISAM;
    ALTER TABLE phpbb_forums_watch ENGINE=MyISAM;
    ALTER TABLE phpbb_groups ENGINE=MyISAM;
    ALTER TABLE phpbb_icons ENGINE=MyISAM;
    ALTER TABLE phpbb_lang ENGINE=MyISAM;
    ALTER TABLE phpbb_log ENGINE=MyISAM;
    ALTER TABLE phpbb_moderator_cache ENGINE=MyISAM;
    ALTER TABLE phpbb_modules ENGINE=MyISAM;
    ALTER TABLE phpbb_poll_options ENGINE=MyISAM;
    ALTER TABLE phpbb_poll_votes ENGINE=MyISAM;
    ALTER TABLE phpbb_posts ENGINE=MyISAM;
    ALTER TABLE phpbb_privmsgs ENGINE=MyISAM;
    ALTER TABLE phpbb_privmsgs_folder ENGINE=MyISAM;
    ALTER TABLE phpbb_privmsgs_rules ENGINE=MyISAM;
    ALTER TABLE phpbb_privmsgs_to ENGINE=MyISAM;
    ALTER TABLE phpbb_profile_fields ENGINE=MyISAM;
    ALTER TABLE phpbb_profile_fields_data ENGINE=MyISAM;
    ALTER TABLE phpbb_profile_fields_lang ENGINE=MyISAM;
    ALTER TABLE phpbb_profile_lang ENGINE=MyISAM;
    ALTER TABLE phpbb_ranks ENGINE=MyISAM;
    ALTER TABLE phpbb_reports ENGINE=MyISAM;
    ALTER TABLE phpbb_reports_reasons ENGINE=MyISAM;
    ALTER TABLE phpbb_search_results ENGINE=MyISAM;
    ALTER TABLE phpbb_search_wordlist ENGINE=MyISAM;
    ALTER TABLE phpbb_search_wordmatch ENGINE=MyISAM;
    ALTER TABLE phpbb_sessions ENGINE=MyISAM;
    ALTER TABLE phpbb_sessions_keys ENGINE=MyISAM;
    ALTER TABLE phpbb_sitelist ENGINE=MyISAM;
    ALTER TABLE phpbb_smilies ENGINE=MyISAM;
    ALTER TABLE phpbb_styles ENGINE=MyISAM;
    ALTER TABLE phpbb_styles_imageset ENGINE=MyISAM;
    ALTER TABLE phpbb_styles_imageset_data ENGINE=MyISAM;
    ALTER TABLE phpbb_styles_template ENGINE=MyISAM;
    ALTER TABLE phpbb_styles_template_data ENGINE=MyISAM;
    ALTER TABLE phpbb_styles_theme ENGINE=MyISAM;
    ALTER TABLE phpbb_topics ENGINE=MyISAM;
    ALTER TABLE phpbb_topics_posted ENGINE=MyISAM;
    ALTER TABLE phpbb_topics_track ENGINE=MyISAM;
    ALTER TABLE phpbb_topics_watch ENGINE=MyISAM;
    ALTER TABLE phpbb_users ENGINE=MyISAM;
    ALTER TABLE phpbb_user_group ENGINE=MyISAM;
    ALTER TABLE phpbb_warnings ENGINE=MyISAM;
    ALTER TABLE phpbb_words ENGINE=MyISAM;
    ALTER TABLE phpbb_zebra ENGINE=MyISAM;
(Information was found here.) It can take a few minutes for these queries to run, so be patient.

Once you have a PHPBB3 installation with MySQL database and MyISAM tables, you can change the "search backend" and create the index. If you have already created an index for the "Fulltext native" backend, delete that index. Do this in the Administration Control Panel by selecting the Maintenance tab and the Search index option.

Now change the backend. Do this in the Administration Control Panel by selecting the General tab and the Search settings page. Under Search backend, choose "Fulltext mysql".

Now create the index for this backend. Do this in the Administration Control Panel by selecting the Maintenance tab and the Search index page again.

Creation of this index is far faster than creation of the "Fulltext native" index (in my case a matter of minutes compared with several hours). A further benefit is that the size of the database will be reduced dramatically (in my case from 248 MB to 86 MB on a board with 60,000 posts).

Once the index has been created, you can search for phrases by enclosing them in quotation marks.

Hope this saves people the grief I had finding the few very simple pieces of information needed.
It's only words...
Wordwizard.com

User avatar
Eelke
QA Team
Posts: 2903
Joined: Thu Dec 20, 2001 8:00 am
Location: NL, Bussum
Name: Eelke Blok
Contact:

Re: Exact phrase searching

Post by Eelke » Tue Feb 12, 2008 8:55 am

Thanks for that. Have you considered adding this to the knowledge base?

User avatar
philwhite
Registered User
Posts: 122
Joined: Wed Aug 22, 2007 12:47 am
Contact:

Re: Exact phrase searching

Post by philwhite » Tue Feb 12, 2008 10:18 am

Good idea, thank you. It would be nice if one of the Admin or mod team cast their eye over it first.
It's only words...
Wordwizard.com

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: Exact phrase searching

Post by A_Jelly_Doughnut » Tue Feb 12, 2008 5:51 pm

The instructions look solid to me.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Exact phrase searching

Post by drathbun » Tue Feb 12, 2008 7:58 pm

Do all of the tables need to be converted to MyISAM or just the post tables?
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Son of a Beach
Registered User
Posts: 294
Joined: Fri Sep 07, 2007 1:36 am
Location: Tasmania
Contact:

Re: Exact phrase searching

Post by Son of a Beach » Tue Feb 12, 2008 8:38 pm

Are there any other advantages or disadvantages between using "fulltext native" and "fulltext mysql"?

User avatar
philwhite
Registered User
Posts: 122
Joined: Wed Aug 22, 2007 12:47 am
Contact:

Re: Exact phrase searching

Post by philwhite » Wed Feb 13, 2008 8:50 am

drathbun wrote:Do all of the tables need to be converted to MyISAM or just the post tables?
I suspect that only the post tables need to be done, but I did them all. Doesn't seem to have harmed anything.
It's only words...
Wordwizard.com

User avatar
philwhite
Registered User
Posts: 122
Joined: Wed Aug 22, 2007 12:47 am
Contact:

Re: Exact phrase searching

Post by philwhite » Wed Feb 13, 2008 8:53 am

Son of a Beach wrote:Are there any other advantages or disadvantages between using "fulltext native" and "fulltext mysql"?
That's discussed at huge length in several other threads. The relative performance appears to vary depending on the size of the board (this only really applies to very big boards). Performance was never an issue for me. I needed exact match searching.
It's only words...
Wordwizard.com

Post Reply

Return to “phpBB Discussion”