Creating sort index takes 2.39990 seconds

MySQL & MariaDB Other MySQL® Questions
1

select this_.WL_LOG_PK as WORKLIST1_164_0_, this_.APPLOG as APPLICAT2_164_0_, this_.test_C_RLOG_COL as test3_164_0_, this_.test_CRLOG_COL as test4_164_0_, this_.TRNLOG as TRANSACT5_164_0_, this_.LOG as HASERRORS6_164_0_, this_.NEW_AR_FLAG_LOG_COL as NEW7_164_0_, this_.OWNER_ID_LOG as OWNER8_164_0_, this_.POS_CHECKER_REMARKS as POS9_164_0_, this_.PR_LOG as PROPOSER10_164_0_, this_.PROPOSER_ID_LOG_FK as PROPOSER11_164_0_, this_.REJ_ID_LOG as RE_164_0_, this_.RLOG as RES13_164_0_, this_.TLOG as TRANSAC14_164_0_, this_.WTE as WFACTION15_164_0_, this_.WSER as WF64_0_, this_.WLOG as WF17_164_0_, this_.WLOG as WF18_164_0_ from T_LOG this_ where this_.PLOG=‘20005555’ and this_.T_TYPE_LOG=‘01’ order by this_.WDATE desc\G

query profile:

±---------------------±---------+
| Status | Duration |
±---------------------±---------+
| starting | 0.000066 |
| checking permissions | 0.000007 |
| Opening tables | 0.000011 |
| init | 0.000029 |
| System lock | 0.000009 |
| optimizing | 0.000011 |
| statistics | 0.000012 |
| preparing | 0.000013 |
| Sorting result | 0.000006 |
| executing | 0.000004 |
| Sending data | 0.000013 |
| Creating sort index | 2.393860 |
| end | 0.000018 |
| query end | 0.000010 |
| closing tables | 0.000015 |
| freeing items | 0.000023 |
| cleaning up | 0.000046 |
±---------------------±---------+

it takes 2.393 seconds for Creating sort index . how can i rectify this?

2

Hi ikonrao;

Try adding an index on INSURANCE_WORKLIST_LOG(PROPOSER_EIA_NUMBER_LOG,TRANSACTION_TYPE_LOG,WFACTION_DATE). If that does not work, you could try an index on just INSURANCE_WORKLIST_LOG(WFACTION_DATE).

-Scott