Uploaded image for project: 'FTS'
  1. FTS
  2. FTS-936

Reorder group by in getQueuesWithPending to benefit from group by with index

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: fts 3.6.7
    • Fix Version/s: fts 3.6.8
    • Component/s: MySQL
    • Security Level: Public Data (This ticket is visible to anyone on the internet and will be indexed by search engines)
    • Labels:
      None

      Description

      The query is almost identical, but compare the explain

      MySQL [fts3_perf_tests]> explain SELECT f.vo_name, f.source_se, f.dest_se FROM t_file f WHERE f.file_state = 'SUBMITTED'  GROUP BY f.source_se, f.dest_se, f.file_state, f.vo_name ORDER BY null;
      +----+-------------+-------+-------+----------------------------------------------+-------------------+---------+------+--------+---------------------------------------+
      | id | select_type | table | type  | possible_keys                                | key               | key_len | ref  | rows   | Extra                                 |
      +----+-------------+-------+-------+----------------------------------------------+-------------------+---------+------+--------+---------------------------------------+
      |  1 | SIMPLE      | f     | range | idx_state_host,idx_link_state_vo,idx_staging | idx_link_state_vo | 570     | NULL | 128190 | Using where; Using index for group-by |
      +----+-------------+-------+-------+----------------------------------------------+-------------------+---------+------+--------+---------------------------------------+
      1 row in set (0.00 sec)
      

      vs the current one

      MySQL [fts3_perf_tests]> explain SELECT f.vo_name, f.source_se, f.dest_se FROM t_file f WHERE f.file_state = 'SUBMITTED' GROUP BY f.vo_name, f.source_se, f.dest_se ORDER BY null;
      +----+-------------+-------+------+----------------------------------------------+----------------+---------+-------+--------+-----------------------------------------------------+
      | id | select_type | table | type | possible_keys                                | key            | key_len | ref   | rows   | Extra                                               |
      +----+-------------+-------+------+----------------------------------------------+----------------+---------+-------+--------+-----------------------------------------------------+
      |  1 | SIMPLE      | f     | ref  | idx_state_host,idx_link_state_vo,idx_staging | idx_state_host | 1       | const | 577836 | Using index condition; Using where; Using temporary |
      +----+-------------+-------+------+----------------------------------------------+----------------+---------+-------+--------+-----------------------------------------------------+
      1 row in set (0.00 sec)
      

      This query accounts for 25.71% of the aggregated time on MySQL, so optimizing it is important.

        Attachments

          Activity

            People

            • Assignee:
              aalvarez Alejandro Alvarez Ayllon
              Reporter:
              aalvarez Alejandro Alvarez Ayllon
              Component Watchers:
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: