Mariadb – composit index and possible keys specific query

indexmariadbquery-performance

i have query like this

select
    date_format(referencedate, '%m/%d/%Y') as PostingDate,
    reference_code,
    "" as DocNo,
    Account_Number,
    detail_desc as Description,
    case
        when DEBT_CREDIT = "D" then sum(SF2356896(value_result, 'E77420A8EE606B00', '10011010100010011011', transact_code))
        else 0
    end as Debit,
    case
        when DEBT_CREDIT = "C" then sum(SF2356896(value_result, 'E77420A8EE606B00', '10011010100010011011', transact_code))
        else 0
    end as Credit,
    left(substring_index(COSTCENTER_CODE, "_", 1),
    9) as job,
    mid(substring_index(COSTCENTER_CODE, "_", 1), 10, length(substring_index(COSTCENTER_CODE, "_", 1))) as jobtask
from
    TPYDJOURNALTRANS_INTERCO_EMP
where
    reference_code = '202007'
    and BUSINESS_UNIT = 'XXX'
    and journal_code = 'JMTHOPERATOR'
group by
    reference_code,
    substring_index(COSTCENTER_CODE, "_", 1),
    account_number,
    detail_desc,
    DEBT_CREDIT

when i explain this query i got results like this

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TPYDJOURNALTRANS_INTERCO_EMP
         type: ref
possible_keys: idx_tpydjournaltrans_ie_BUSINESSUNIT2,idx_tpydjournaltrans_ie_journalcode,idx_tpydjournaltrans_ie_reference_code,idx_tpydjournaltrans_ie_BUSINESSUNIT
          key: idx_tpydjournaltrans_ie_BUSINESSUNIT
      key_len: 139
          ref: const,const,const
         rows: 1160572
        Extra: Using index condition; Using where; Using temporary; Using filesort

and this index on table TPYDJOURNALTRANS_INTERCO_EMP

---------+
| Table                        | Non_unique | Key_name                               | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------------+------------+----------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            1 | period_code     | A         |         972 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            2 | processdate     | A         |        2436 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            3 | journal_code    | A         |        2436 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            4 | account_number  | A         |       27269 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            5 | debt_credit     | A         |       20019 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            6 | costcenter_code | A         |      441773 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | ix_tuned_transiss_tuned4               |            7 | detail_desc     | A         |     1656648 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_EMPID          |            1 | EMP_ID          | A         |      125030 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_taxtype        |            1 | tax_type        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_jobstatuscode  |            1 | jobstatuscode   | A         |          44 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_BUSINESSUNIT2  |            1 | BUSINESS_UNIT   | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_referencedate  |            1 | referencedate   | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_journalcode    |            1 | journal_code    | A         |        4420 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_reference_code |            1 | REFERENCE_CODE  | A         |          14 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_BUSINESSUNIT   |            1 | REFERENCE_CODE  | A         |        4784 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_BUSINESSUNIT   |            2 | BUSINESS_UNIT   | A         |       10370 |     NULL | NULL   | YES  | BTREE      |         |               |
| tpydjournaltrans_interco_emp |          1 | idx_tpydjournaltrans_ie_BUSINESSUNIT   |            3 | journal_code    | A         |       23922 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------------------+------------+----------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+------

EDIT
this is results from analyze

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 29936,
    "table": {
      "table_name": "x",
      "access_type": "ref",
      "possible_keys": [
        "idx_tpydjournaltrans_ie_BUSINESSUNIT2",
        "idx_tpydjournaltrans_ie_journalcode",
        "idx_tpydjournaltrans_ie_reference_code",
        "idx_tpydjournaltrans_ie_BUSINESSUNIT"
      ],
      "key": "idx_tpydjournaltrans_ie_BUSINESSUNIT",
      "key_length": "139",
      "used_key_parts": ["REFERENCE_CODE", "BUSINESS_UNIT", "journal_code"],
      "ref": ["const", "const", "const"],
      "r_loops": 1,
      "rows": 1160572,
      "r_rows": 566598,
      "r_total_time_ms": 2402.5,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "x.REFERENCE_CODE = '202007' and x.journal_code = 'JMTHOPERATOR' and x.BUSINESS_UNIT = 'ISS'"
    }
  }
}

my question is
query on top is effective using composit index idx_tpydjournaltrans_ie_BUSINESSUNIT because on explain my query using this index ?

and if my query using composit index there is need more single column index or not ?

Best Answer

Yes your idx_tpydjournaltrans_ie_BUSINESSUNIT index is being used (I assume length 139 corresponds the the length of the three index components).

Other single element index elements may be assisting other queries. For each table reference in a query, a single index is used.

The idx_tpydjournaltrans_ie_BUSINESSUNIT index corresponds to the 3 elements of the WHERE clause.

The GROUP BY is causing the sort of ~1M rows returned by the WHERE clause so is probably a slow point that isn't using an index.

COSTCENTER_CODE seems to be a combination of elements and I recommend using generated columns so either the component or totals an be accessed individually.

Once this is done the costcenter_code_component, account_number, detail_desc, DEBT_CREDIT can be appended to the idx_tpydjournaltrans_ie_BUSINESSUNIT index to reduce the need to sorts as they are already sorted in the index.

Note at this point your index is getting quite long. Ensure all the columns are to their minimum length and have the smallest character set required.

Related Question