1. 31 Dec, 2018 2 commits
  2. 04 Dec, 2018 2 commits
  3. 03 Dec, 2018 2 commits
  4. 29 May, 2018 4 commits
  5. 13 Nov, 2017 4 commits
  6. 16 Oct, 2017 2 commits
    • Paweł Bogusławski committed
    • In OTRS setup with many queues and many thousands of unarchieved tickets
      MySQL 5.5 sometimes does not use best index to do fulltext searching
      which may cause bad performance problems (i.e. full text searches
      of unarchieved tickets 3x longer).
      
      When MySQL chooses wrong index we have queue_group_id used
      not ticket_archive_flag
      
      ```
      mysql -e "DESCRIBE SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st  INNER JOIN article_search art ON st.id = art.ticket_id  INNER JOIN queue sq ON sq.id = st.queue_id  WHERE 1=1 AND sq.group_id IN (1,10,100,101,102,104,105,106,107,108,109,11,110,111,112,113,115,116,117,118,119,12,120,121,122,123,125,126,127,128,129,13,130,131,132,133,134,135,136,137,138,139,14,140,141,142,143,144,145,146,147,148,149,15,150,151,152,153,154,155,156,157,158,159,16,160,161,163,164,165,166,167,168,169,17,170,171,172,173,176,177,178,179,18,180,181,182,183,184,185,186,187,188,189,19,190,191,192,193,194,195,198,199,20,201,202,203,21,22,23,24,25,26,27,28,29,30,39,4,40,41,42,43,44,45,46,47,48,49,5,50,51,52,53,54,55,56,57,58,59,6,60,61,62,66,67,68,69,7,70,72,73,74,75,76,77,78,79,8,80,81,82,83,84,85,86,87,88,89,9,90,91,92,93,94,95,96,97,98)  AND archive_flag = 0 AND (((art.a_body LIKE '%test%' ) ) OR ((art.a_cc LIKE '%test%' ) ) OR ((art.a_from LIKE '%test%' ) ) OR ((art.a_subject LIKE '%test%' ) ) OR ((art.a_to LIKE '%test%' ) )) ORDER BY st.create_time_unix DESC LIMIT 2000;" otrs
      +----+-------------+-------+-------+---------------------------------------------+--------------------------+---------+------------+------+-----------------------------------------------------------+
      | id | select_type | table | type  | possible_keys                               | key                      | key_len | ref        | rows | Extra                                                     |
      +----+-------------+-------+-------+---------------------------------------------+--------------------------+---------+------------+------+-----------------------------------------------------------+
      |  1 | SIMPLE      | sq    | index | PRIMARY,queue_group_id                      | queue_group_id           | 4       | NULL       |  217 | Using where; Using index; Using temporary; Using filesort |
      |  1 | SIMPLE      | st    | ref   | PRIMARY,ticket_archive_flag,ticket_queue_id | ticket_queue_id          | 4       | otrs.sq.id | 1717 | Using where                                               |
      |  1 | SIMPLE      | art   | ref   | article_search_ticket_id                    | article_search_ticket_id | 8       | otrs.st.id |    1 | Using where; Distinct                                     |
      +----+-------------+-------+-------+---------------------------------------------+--------------------------+---------+------------+------+-----------------------------------------------------------+
      ```
      
      Its possible to suggest MySQL to use proper index like this:
      
      ```
      mysql -e "DESCRIBE SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st USE INDEX (ticket_archive_flag) INNER JOIN article_search art ON st.id = art.ticket_id  INNER JOIN queue sq ON sq.id = st.queue_id  WHERE 1=1 AND sq.group_id IN (1,10,100,101,102,104,105,106,107,108,109,11,110,111,112,113,115,116,117,118,119,12,120,121,122,123,125,126,127,128,129,13,130,131,132,133,134,135,136,137,138,139,14,140,141,142,143,144,145,146,147,148,149,15,150,151,152,153,154,155,156,157,158,159,16,160,161,163,164,165,166,167,168,169,17,170,171,172,173,176,177,178,179,18,180,181,182,183,184,185,186,187,188,189,19,190,191,192,193,194,195,198,199,20,201,202,203,21,22,23,24,25,26,27,28,29,30,39,4,40,41,42,43,44,45,46,47,48,49,5,50,51,52,53,54,55,56,57,58,59,6,60,61,62,66,67,68,69,7,70,72,73,74,75,76,77,78,79,8,80,81,82,83,84,85,86,87,88,89,9,90,91,92,93,94,95,96,97,98)  AND archive_flag = 0 AND (((art.a_body LIKE '%test%' ) ) OR ((art.a_cc LIKE '%test%' ) ) OR ((art.a_from LIKE '%test%' ) ) OR ((art.a_subject LIKE '%test%' ) ) OR ((art.a_to LIKE '%test%' ) )) ORDER BY st.create_time_unix DESC LIMIT 2000;" otrs
      +----+-------------+-------+--------+--------------------------+--------------------------+---------+------------------+--------+----------------------------------------------+
      | id | select_type | table | type   | possible_keys            | key                      | key_len | ref              | rows   | Extra                                        |
      +----+-------------+-------+--------+--------------------------+--------------------------+---------+------------------+--------+----------------------------------------------+
      |  1 | SIMPLE      | st    | ref    | ticket_archive_flag      | ticket_archive_flag      | 2       | const            | 509124 | Using where; Using temporary; Using filesort |
      |  1 | SIMPLE      | sq    | eq_ref | PRIMARY,queue_group_id   | PRIMARY                  | 4       | otrs.st.queue_id |      1 | Using where; Distinct                        |
      |  1 | SIMPLE      | art   | ref    | article_search_ticket_id | article_search_ticket_id | 8       | otrs.st.id       |      1 | Using where; Distinct                        |
      +----+-------------+-------+--------+--------------------------+--------------------------+---------+------------------+--------+----------------------------------------------+
      ```
      
      This mod adds hint for MySQL database query optimizer to avoid its
      wrong decisions and use ticket_archive_flag in such queries.
      
      Related: #114
      Author-Change-Id: IB#1073573
      Paweł Bogusławski committed
  7. 05 Oct, 2017 4 commits
  8. 04 Oct, 2017 4 commits
  9. 02 Oct, 2017 2 commits
  10. 03 Aug, 2017 2 commits
  11. 05 Jun, 2017 2 commits
  12. 08 May, 2017 2 commits
  13. 31 Mar, 2017 7 commits
  14. 30 Mar, 2017 1 commit