A "SELECT" query executed by the QuickSearch page took 72 seconds.
# Time: 2021-07-20T06:52:32.025610Z
# User@Host: root[root] @ localhost [] Id: 3861
# Query_time: 71.800235 Lock_time: 0.000238 Rows_sent: 25 Rows_examined: 860558
SET timestamp=1626763952;
select this_.QB_ID as QB_ID1_4_0_, this_.QB_BEGIN_DATE as QB_BEGIN2_4_0_, this_.QB_CANCELLER_ID as QB_CANC16_4_0_, this_.QB_CONFIGURATION_ID as QB_CONF17_4_0_, this_.QB_DESCRIPTION as QB_DESCR3_4_0_, this_.QB_DURATION as QB_DURAT4_4_0_, this_.QB_ERROR_MESSAGE as QB_ERROR5_4_0_, this_.QB_MASTER_NODE_ADDRESS as QB_MASTE6_4_0_, this_.QB_PROMOTED_FROM_ID as QB_PROM18_4_0_, this_.QB_REPOSITORY_RUNTIMES as QB_REPOS7_4_0_, this_.QB_REQUESTER_ID as QB_REQU19_4_0_, this_.QB_SCHEDULED as QB_SCHED8_4_0_, this_.bld_sec_var_vals as bld_sec_9_4_0_, this_.QB_SHORT_BRANCH as QB_SHOR10_4_0_, this_.QB_STATUS as QB_STAT11_4_0_, this_.QB_STATUS_DATE as QB_STAT12_4_0_, this_.QB_STEP_RUNTIMES as QB_STEP13_4_0_, this_.QB_VERSION as QB_VERS14_4_0_, this_.QB_WAIT_DURATION as QB_WAIT15_4_0_ from QB_BUILD this_ where (lower(this_.QB_VERSION) like '%xxxxxxx%') and this_.QB_BEGIN_DATE>='2020-06-01 00:00:00' order by this_.QB_ID desc limit 25;
When I added "this_.QB_BEGIN_DATE" to "ORDER BY" clause, it took only 7 seconds :slight_smile:
# Time: 2021-07-20T06:53:47.043361Z
# User@Host: root[root] @ localhost [] Id: 3861
# Query_time: 6.793209 Lock_time: 0.000230 Rows_sent: 25 Rows_examined: 1754462
SET timestamp=1626764027;
select this_.QB_ID as QB_ID1_4_0_, this_.QB_BEGIN_DATE as QB_BEGIN2_4_0_, this_.QB_CANCELLER_ID as QB_CANC16_4_0_, this_.QB_CONFIGURATION_ID as QB_CONF17_4_0_, this_.QB_DESCRIPTION as QB_DESCR3_4_0_, this_.QB_DURATION as QB_DURAT4_4_0_, this_.QB_ERROR_MESSAGE as QB_ERROR5_4_0_, this_.QB_MASTER_NODE_ADDRESS as QB_MASTE6_4_0_, this_.QB_PROMOTED_FROM_ID as QB_PROM18_4_0_, this_.QB_REPOSITORY_RUNTIMES as QB_REPOS7_4_0_, this_.QB_REQUESTER_ID as QB_REQU19_4_0_, this_.QB_SCHEDULED as QB_SCHED8_4_0_, this_.bld_sec_var_vals as bld_sec_9_4_0_, this_.QB_SHORT_BRANCH as QB_SHOR10_4_0_, this_.QB_STATUS as QB_STAT11_4_0_, this_.QB_STATUS_DATE as QB_STAT12_4_0_, this_.QB_STEP_RUNTIMES as QB_STEP13_4_0_, this_.QB_VERSION as QB_VERS14_4_0_, this_.QB_WAIT_DURATION as QB_WAIT15_4_0_ from QB_BUILD this_ where (lower(this_.QB_VERSION) like '%xxxxxxx%') and this_.QB_BEGIN_DATE>='2020-06-01 00:00:00' order by this_.QB_ID, this_.QB_BEGIN_DATE desc limit 25;