2

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 ?

1
  • good question. SHOW CREATE TABLE tpydjournaltrans_interco_emp would of partially useful but try to include table structure in further questions. – danblack Aug 24 '20 at 4:33
2

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.

6
  • thanks for update, so do yo mean for costcenter_code need generate column with value substring_index ? – febry Aug 24 '20 at 4:51
  • yes. And use that generated column name in the group by, index and the results. – danblack Aug 24 '20 at 5:05
  • after me more debug that query, i got this function make slow 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 this function for decrypt data from value_result. this need index too ? – febry Aug 24 '20 at 6:20
  • Without that expression the entire results can come straight from the the index so its not surprising it adds to the query time. Its hard to tell if its the fetching from the main (PK) clustered index is causing the extra time or the the encryption overhead. To eliminate the former add value_result, transact_code to the index to eliminate the PK lookup. Look at explain and time differences. – danblack Aug 24 '20 at 6:37
  • this example record from field value_result 1E28E1E70227C32206520DED03803DA3 so may i need index for this field ? – febry Aug 25 '20 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.