Performance issues, index missing on table article_search_index ?

[nilskm]

My users experience searches in otrs quite slow. A full text search takes more than 20 seconds each time. I checked mysql slow log and found this:

Code: Select all

SELECT DISTINCT st.id, st.tn, st.create_time 
FROM ticket st  INNER JOIN article art ON st.id = art.ticket_id  
LEFT JOIN article_search_index ArticleFulltext ON art.id = ArticleFulltext.article_id  
INNER JOIN queue sq ON sq.id = st.queue_id 
 WHERE 1=1 AND sq.group_id IN (1,10,11,12,13,14,15,16,17,2,3,4,5,6,7,8) 
 AND (((ArticleFulltext.article_value LIKE '%words searched for%'  OR st.title LIKE '%words searched for%' ) ))  
 ORDER BY st.create_time DESC LIMIT 2000;

Shouldn't there be a full text index on the column article_search_index.article_value ?

I don't think it's in issue with "fulltextIndexRebuild" and I do have the setting ArticleStorageFS

Using:
znuny 6.5.1
MariaDB 10.2.38
Ram: 64GB
SSD-disk.
24 vCPU
Tickets: 190 000

[nilskm]

I just tried to put an index on that field but didn't make a difference, so that wasn't the problem.

I just realized that the query also should've had

Code: Select all

AND archive_flag = 0 

Am I right? But this is not the case. I've tried to enable and disable the setting for Ticket::ArchiveSystem. I don't understand, what I'm doing wrong? Is there anything else that needs to be done?