Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-15968

MySQL Fulltext search index slows update to unworkable levels on large-ish forums



    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 3.2.5
    • None
    • Update system
    • None


      I filed this as a bug and not an improvement only because of how severe the slowdown is.

      The starting point is a 3.0.x install with >5,000,000 posts using MySQL Fulltext as the search backed. Will switch to Sphinx after the update, but the search is surprisingly speedy on MySQL Fulltext.

      For schema changes, if there is a Fulltext index on the table, InnoDB will create a new empty table with the new schema and then copy all the data to it, rebuilding the index.

      During the update, the following queries were run:

      ALTER TABLE `phpbb_posts` ADD COLUMN `post_visibility` tinyint(3) DEFAULT '0' NOT NULL
      ALTER TABLE `phpbb_posts` ADD COLUMN `post_delete_time` int(11) UNSIGNED DEFAULT '0' NOT NULL
      ALTER TABLE `phpbb_posts` ADD COLUMN `post_delete_reason` varchar(255) DEFAULT '' NOT NULL

      With the index in place, each query takes between 12 and 24 hours to complete. Without the index, it takes ~4 minutes

      Installing Schema: \phpbb\db\migration\data\v310\softdelete_p1; Time: 192.13 seconds
      Installing Schema: \phpbb\db\migration\data\v310\softdelete_p1; Time: 202.60 seconds
      Installing Schema: \phpbb\db\migration\data\v310\softdelete_p1; Time: 201.58 seconds

      So the perfect solution is that for forums over a certain size that use Fulltext, if the update changes the posts table, we drop the index and then either rebuild it or probably just instruct the admin to rebuild it after. Another option is to warn the admin it will take forever otherwise and instruct them to drop the index manually and then recreate it after.





            Unassigned Unassigned
            Marshalrusty Yuriy Rusko
            0 Vote for this issue
            2 Start watching this issue