Re: Queriy taking long time in OTRS
Hi,
Are you sure that it is the sorting that takes most of the time for your query, and not the fact that the query plan involves reading 3 million rows from the article table?
In order to avoid sorting, you will need a query plan that starts with the table of the column you are sorting on, ticket. You can force such a plan by replacing "INNER JOIN" with "STRAIGHT_JOIN". However, since there is no predicate for article in your WHERE clause, it will involve reading a lot of rows that will not be part of the result.
I think your best bet is to avoid the table scan on article. In order to be able to use the existing FULLTEXT index, you will have to replace the LIKE expressions with a MATCH expression as Rick suggests. Also, the MATCH expression need to list all columns of the index. Hence, if you want to only search for occurences in the a_body column, you will need a FULLTEXT index for just this column.
Hope this helps,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway