Huge table index_rel never stops growing
On one of our webservers we have a lot of typo3-based sites. Some of them make use of the indexedsearch extension, which uses some database tables to store the indexed data. These tables can grow really huge .
Bad performance for search queries
The table index_rel grows very fast, having over 6.000.000 entries for some typo3 installations. Even a simple search takes over 30 seconds to complete. I searched for an elegant solution with very little success.
Our current solution: periodical brute force
Here is a brute force solution: a ruby-script that finds all databases that have a index_rel-table and truncates all indexed search tables if they reach a given size. The tables will be automatically refilled every time a page is accessed.
The script truncates the “cache tables” also, the “indexed search tables” will not be rebuild by public requests if only the cache for a given page is returned by a request.
If you want to refill the search instantly index can write a script the crawls the site using wget —spider or something similar.
We call this via cron once a week – every friday night