<div>
<h3 class="header_margin">Issue</h3> <p class="text_margin"> 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. </p>
<h3 class="header_margin">Possible Solution</h3> <p class="text_margin"> This guide explains how to safely drop and recreate all FULLTEXT indexes in your PaperOffice MariaDB database using <a href="https://www.heidisql.com/" target="_blank">HeidiSQL</a>. </p>
<p class="disclaimer"> 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. </p>
<h3 class="header_margin">Step-by-Step Guide</h3>
<ol>
<li class="custom_li">
<strong>Install HeidiSQL</strong><br>
Download and install HeidiSQL from <a href="https://www.heidisql.com/" target="_blank">https://www.heidisql.com/</a>.
</li>
<li class="custom_li">
<strong>Identify Your PaperOffice Database Name</strong><br>
<ul>
<li class="custom_li">Open HeidiSQL and connect to your MariaDB server.</li>
<li class="custom_li">In the left panel, look for the database used by PaperOffice. The name is unique for every installation (e.g., <code>xxfwxukcvgahspyehjztxyfuf</code>).</li>
</ul>
</li>
<li class="custom_li">
<strong>Backup Your Database</strong><br>
<ul>
<li class="custom_li">Right-click your PaperOffice database in HeidiSQL.</li>
<li class="custom_li">Select "Export database as SQL".</li>
<li class="custom_li">Save the backup file to a safe location.</li>
</ul>
</li>
<li class="custom_li">
<strong>Prepare the FULLTEXT Index Rebuild Script</strong><br>
<ul>
<li class="custom_li">Open a new query tab in HeidiSQL.</li>
<li class="custom_li">Copy and paste the following script, replacing <code>xxfwxukcvgahspyehjztxyfuf</code> with your actual database name:</li>
</ul>
<pre>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);
</pre>
<ul>
<li class="custom_li"><strong>Note:</strong> This command can take a while, depending on your database size.</li>
</ul>
</li>
<li class="custom_li">
<strong>Execute the Script</strong><br>
Click the "Run" button in HeidiSQL to execute the script.
</li>
<li class="custom_li">
<strong>Restart the MariaDB Service</strong><br>
<ul>
<li class="custom_li">After running the script, restart the MariaDB service to ensure all changes are fully applied and any cached index data is refreshed.</li>
<li class="custom_li">
<strong>On Windows:</strong>
<ul>
<li class="custom_li">Open the Services app (press <kbd>Win</kbd>+<kbd>R</kbd>, type <code>services.msc</code>, and press Enter).</li>
<li class="custom_li">Find "MariaDB" in the list, right-click, and select "Restart".</li>
</ul>
</li>
<li class="custom_li">
<strong>Or via command prompt:</strong>
<pre>net stop MariaDB
net start MariaDB</pre>
</li>
</ul>
</li>
<li class="custom_li">
<strong>Verify the Results</strong><br>
Test the search and content loading features in PaperOffice. Performance should be improved.
</li>
<li class="custom_li">
<strong>If You Encounter Issues</strong><br>
Restore your database from the backup you created in step 3.
</li>
</ol>
<h3 class="header_margin">Summary</h3> <p class="text_margin"> 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. </p> </div>