In order to provide multi-content search without locking tables, XenForo maintains a separate search index table with a MySQL FullText index. Sometimes it is necessary to rebuild that index, such as when major changes to MySQL configuration are made (like changing the minimum searchable word length) or after completing an import of threads and posts from another system.
The search index rebuilding process is intensive and can take quite a while for large sites, but there are a few things that you can do to speed it up if you know what you are doing.
The first thing to do is to increase the number of items to be processed in each rebuild cycle. The default is 500 items, but with a fast server you may see a speed boost by bumping that number up to 5,000 or even 10,000 items per page.
To do this, you will need to run a query manually to disable the FullText index before you start the rebuild process from the XenForo Admin Control Panel. The query is as follows:
Having run this query, you can start the rebuild process from the XenForo control panel.
After the rebuild process has completed, you must turn the FullText index back on before search will work. This will require another query to be run manually:
This query will take quite a while to complete, especially on a large database, but when it's done, search will be ready to use.
To get some idea of how long the query might take, I recently ran the query on a 5.5 million post database on a test machine here (Core 2 Quad 2.4GHz, 8GB RAM, average speed SATA hard drive) and the ENABLE KEYS query took just over two hours to complete.
It is important to note that if you forget to re-enable the FullText index using ENABLE KEYS, the search system on your site will not work at all, which is why we recommend disabling keys to experts only.
The search index rebuilding process is intensive and can take quite a while for large sites, but there are a few things that you can do to speed it up if you know what you are doing.
The first thing to do is to increase the number of items to be processed in each rebuild cycle. The default is 500 items, but with a fast server you may see a speed boost by bumping that number up to 5,000 or even 10,000 items per page.
Secondly, and this will have a big impact, is dividing up the work that MySQL has to do. The search index rebuild process must do two jobs - generating the search index data from the original content, and then inserting that data into the search index table. Normally, part of this insert process involves MySQL generating its own FullText index for the search index data, but you can defer this process until later by temporarily disabling the FullText index.To do this, you will need to run a query manually to disable the FullText index before you start the rebuild process from the XenForo Admin Control Panel. The query is as follows:
Code:
ALTER TABLE xf_search_index DISABLE KEYS;
After the rebuild process has completed, you must turn the FullText index back on before search will work. This will require another query to be run manually:
Code:
ALTER TABLE xf_search_index ENABLE KEYS;
To get some idea of how long the query might take, I recently ran the query on a 5.5 million post database on a test machine here (Core 2 Quad 2.4GHz, 8GB RAM, average speed SATA hard drive) and the ENABLE KEYS query took just over two hours to complete.
It is important to note that if you forget to re-enable the FullText index using ENABLE KEYS, the search system on your site will not work at all, which is why we recommend disabling keys to experts only.