We've designed OTRS extension that mitagates problem with slow full text search (FTS) in OTRS/MySQL with large number of tickets (problem may be similar for other databases).
Standard OTRS uses non-indexed SQL queries for text pattern searching (i.e. in toolbar or in Ticket > Search); this forces SQL server to do slow full table scans and may hurt overall system performance if you allow agents to do FTS on large number of tickets.
To stop agents from killing your DB server with many FTS, you can try to enable ticket archiving and hope, that agent's won't need to search for archived tickets (if they will search for archived tickets - performance problem will occur again).
Our extension allows to use external, open source, FTS optimized engine called Sphinx for searching using OTRS toolbar fulltext search field - other fields (i.e. in Ticket > Search) are not compatible with this extension and use standard OTRS way of searching. Our extension uses SphinxQL connector (MySQL compatible) so no need for extra sphinx client libs on OTRS side.
You don't need this extension if you have small number of tickes or you are happy with your current system performance. As soon as you'll notice slow FTS in your mysql-slow.log and/or FTS caused CPU bottlenecks on your DB server - give this extension a try.
Sample results for searching in test OTRS 3.3 system with 3,6 mln tickets and 10,3 mln articles on modern server (without results page generating time - only searching time reported by MySQL and Sphinx):
Searching for word not present in any article: 62,906s 0,001s
Searching for two popular words: 60,686s 0,024s
Always test changes in test OTRS installation and create all necessary backups before applying to production systems.
This instruction is for experienced OTRS admins/developers. Use it on your own risk.
Install our extension introduced in 950af9ca (for OTRS 3.3) or in #15 (for OTRS 5); patching required - we don't provide opm packages; you can buy our help if required - see below), rebuild OTRS config and delete its cache.
Go to SysConfig and configure connection to your Sphinx instance in Ticket::SearchIndex::Sphinx, turn on Ticket::EventModulePost###99-ArticleSearchIndexUpdateAttr, set Ticket::SearchIndexModule = SphinxAndStaticDB; now indexing of new tickets should work in both: StaticDB and Sphinx.
To index all existing tickets ib OTRS 3.3, use otrs.RebuildFulltextIndex.pl (see 950af9ca for extra -m option - this allows you to update only Sphinx index without rebuilding StaticDB which may be unnecessary and slow). In OTRS 5 use otrs.Console.pl Maint::Ticket::FulltextIndexRebuild (see #15 for extra --main-index-only option - same meaning like -m in OTRS 3.3).
During rebuilding Sphinx index leave update_only set to 1 in Ticket::SearchIndex::Sphinx to force all searches to use StaticDB and all updates feed both: Sphinx and StaticDB. After index rebuild is done - turn on Sphinx searches setting update_only to 0 and enjoy fast searches using Sphinx and its advanced logical operators.
Enable Ticket::ArchiveSystem::ToolBarSearchFulltextAllTickets in SysConfig if you want toolbar fulltext search function to browse all (archived and unarchived) tickets (OTRS by default allows you to search for unarchived tickets using this field).
In case you need further help with this extension setup, we also provide professional, commercial support and services for OTRS system. Please contact us if you need our staff to help you.