Typo3 indexed_search and tt_news - index_rel growth
The Typo3 extension tt_news is causing problems with indexed_search. The table index_rel grows quickly to an enormous size, 28 million rows on one of my sites. This caused two problems:
- the nightly backups took a lot of time, the automated restore on the standby machine wouldn't even finish anymore.
- Typo3 search generated huge SQL queries that would bring down the MySQL server.
There is a lot of content on this site, but nowhere near 28 million "items" (whatever is in index_rel I do not know).
The problem is said to be caused by archived news items that are indexed again on every access. Few people access archived news items, but search bots do access them all the time.
Here's my hackerish fix for this: The sites have most of their "real" accesses during the week, search bots access the site every day. Typo3 indexes a page that is not in the index automatically upon the first access. So every Friday night at 23:30 I truncate these tables and have them "refilled" by search bots:
truncate table index_fulltext;
truncate table index_phash;
truncate table index_section;
truncate table index_words;
truncate table index_rel;
truncate table index_grlist;
Xing
Facebook
Google
identi.ca
LinkedIn
Twitter