Hi, today I was thinking about improvements for fulltext search (special body of article) which I want to discuss here. :) Current situation / Use case: o OTRS 2.x o Intel Core2 2.40GHz A fulltext search on body with "smith" takes about 15 sek on large installations (done by normal LIKE search). Has somebody ideas how to improve this? I do have 3 ideas. a) Striping/Unique Content -------------------------- The idea is quite easy. Just creating a mirror article table (e. g. article_search) and store all bodies of the article table with unique words of one article in the article_search table. So OTRS can use this table 1:1 for fulltext searches. b) Word-Table/Ref-Word-Article-Table ------------------------------------ This idea is to put all used words in article body into a search_word (id,word) table and put the relations what words are used in the article body into a search_word_article (word_id,article_id). So OTRS can use this table for fulltext searches. c) Create Index of frequent search words ---------------------------------------- This idea is to put all frequently searched words in an extra index. And do not frequently search words in real time. Result with about 12.000 tickets and 50.000 article: | article |a) article | b)search_words | c)dedicated | (default) | mirror uniq | and ref table | search_word | | words | | index ------------+--------------+---------------+---------------- +--------------- Storage Size| 60M | 16M | 23M | 8M/ 2000words ------------+--------------+---------------+---------------- +--------------- Search Time | 4s | 2s | 1s | Index 1s | | | | No Index 4s ------------+--------------+---------------+---------------- +--------------- Time to | 0 min | 30 min | 2.5 h | 1 h create Index| | | | ------------+--------------+---------------+---------------- +--------------- Result with about 70.000 tickets and 240.000 article: | article |a) article | b)search_words | c)dedicated | (default) | mirror uniq | and ref table | search_words | | words | | ------------+--------------+---------------+---------------- +--------------- Storage Size| 440M | 121M | 394M | 50M/ 2000words ------------+--------------+---------------+---------------- +--------------- Search Time | 17s | 5.8s | 48s | Index 3.0s | | | | No Index 17s ------------+--------------+---------------+---------------- +--------------- Time to | 0 min | 1 h | 9 h | 4 h create Index| | | | ------------+--------------+---------------+---------------- +--------------- So in my opinion for small setups b) would work very fine but would not scale to lager installations. c) looks good for large installations till not indexed words are searched (e. g. searches for CVE oder other numbers would always use no indexed search). a) looks robust to both, large and small installations. Any other ideas or questions? -Martin -- ((otrs)) :: OTRS AG :: Europaring 4 :: D - 94315 Straubing Fon: +49 (0) 9421 56818 0 :: Fax: +49 (0) 9421 56818 18 http://www.otrs.com/ :: Communication with success! Address of record: Bad Homburg Local Court: Bad Homburg, HRB 10751 Tax number: 003/240/97505 Chairman: Burchard Steinbild Chief Executive Officer: André Mindermann