Issue

If you experience slow search results or content loading in PaperOffice, the cause may be outdated or fragmented FULLTEXT indexes in your MariaDB database. Rebuilding these indexes can significantly improve search performance.

Possible Solution

This guide explains how to safely drop and recreate all FULLTEXT indexes in your PaperOffice MariaDB database using HeidiSQL.

Disclaimer: Proceed at your own risk. Always make a full backup of your database before making any changes. You are responsible for any issues or data loss resulting from these actions.

Step-by-Step Guide

1. Install HeidiSQL
   Download and install HeidiSQL from https://www.heidisql.com/.
2. Identify Your PaperOffice Database Name
   
   • Open HeidiSQL and connect to your MariaDB server.
   • In the left panel, look for the database used by PaperOffice. The name is unique for every installation (e.g., xxfwxukcvgahspyehjztxyfuf).
3. Backup Your Database
   
   • Right-click your PaperOffice database in HeidiSQL.
   • Select "Export database as SQL".
   • Save the backup file to a safe location.
4. Prepare the FULLTEXT Index Rebuild Script
   
   • Open a new query tab in HeidiSQL.
   • Copy and paste the following script, replacing xxfwxukcvgahspyehjztxyfuf with your actual database name:

   USE `xxfwxukcvgahspyehjztxyfuf`;
   
   -- Rebuild FULLTEXT indexes for all relevant tables
   
   -- Table: categories
   ALTER TABLE `categories`
     DROP INDEX `name`,
     ADD FULLTEXT INDEX `name` (`name`);
   
   -- Table: documents
   ALTER TABLE `documents`
     DROP INDEX `name`,
     DROP INDEX `xml_data`,
     DROP INDEX `type`,
     DROP INDEX `original_location`,
     DROP INDEX `keywords`,
     DROP INDEX `security_geolocation`,
     DROP INDEX `contacts`,
     DROP INDEX `description`,
     ADD FULLTEXT INDEX `name` (`name`),
     ADD FULLTEXT INDEX `xml_data` (`xml_data`),
     ADD FULLTEXT INDEX `type` (`type`),
     ADD FULLTEXT INDEX `original_location` (`original_location`),
     ADD FULLTEXT INDEX `keywords` (`keywords`),
     ADD FULLTEXT INDEX `security_geolocation` (`security_geolocation`),
     ADD FULLTEXT INDEX `contacts` (`contacts`),
     ADD FULLTEXT INDEX `description` (`description`);
   
   -- Table: documents_annotations
   ALTER TABLE `documents_annotations`
     DROP INDEX `text`,
     DROP INDEX `note_id`,
     DROP INDEX `task_id`,
     DROP INDEX `reminder_id`,
     DROP INDEX `contact_id`,
     DROP INDEX `tag_id`,
     DROP INDEX `events`,
     ADD FULLTEXT INDEX `text` (`text`),
     ADD FULLTEXT INDEX `note_id` (`note_id`),
     ADD FULLTEXT INDEX `task_id` (`task_id`),
     ADD FULLTEXT INDEX `reminder_id` (`reminder_id`),
     ADD FULLTEXT INDEX `contact_id` (`contact_id`),
     ADD FULLTEXT INDEX `tag_id` (`tag_id`),
     ADD FULLTEXT INDEX `events` (`events`);
   
   -- Table: documents_notes
   ALTER TABLE `documents_notes`
     DROP INDEX `content`,
     ADD FULLTEXT INDEX `content` (`content`);
   
   -- Table: documents_ocr
   ALTER TABLE `documents_ocr`
     DROP INDEX `ocr_text`,
     DROP INDEX `smartspell`,
     ADD FULLTEXT INDEX `ocr_text` (`ocr_text`),
     ADD FULLTEXT INDEX `smartspell` (`smartspell`);
   
   -- Table: documents_tasks
   ALTER TABLE `documents_tasks`
     DROP INDEX `this_match`,
     ADD FULLTEXT INDEX `this_match` (`content`, `todo_subject`);
   
   -- Table: documents_barcodes
   ALTER TABLE `documents_barcodes`
     DROP INDEX `barcode_value`,
     ADD FULLTEXT INDEX `barcode_value` (`barcode_value`);
   
   -- Table: documents_udfs
   ALTER TABLE `documents_udfs`
     DROP INDEX `value`,
     ADD FULLTEXT INDEX `value` (`value`);
         

   • Note: This command can take a while, depending on your database size.
5. Execute the Script
   Click the "Run" button in HeidiSQL to execute the script.
6. Restart the MariaDB Service
   
   • After running the script, restart the MariaDB service to ensure all changes are fully applied and any cached index data is refreshed.
   • On Windows:
     • Open the Services app (press Win+R, type services.msc, and press Enter).
     • Find "MariaDB" in the list, right-click, and select "Restart".
   • Or via command prompt:

     net stop MariaDB
     net start MariaDB

7. Verify the Results
   Test the search and content loading features in PaperOffice. Performance should be improved.
8. If You Encounter Issues
   Restore your database from the backup you created in step 3.

Summary

Rebuilding FULLTEXT indexes can resolve slow search and content loading in PaperOffice. Always back up your database before making changes, and use HeidiSQL for a user-friendly experience.