It is odd that adding an extra order by criteria can increase the performance. Also the result set has been filtered by configuration id, which makes ordering by configuration id redundant.

I'd suggest to remove unnecessary builds via build cleanup strategy periodically to improve performance.

We always cleanup strategy periodically.

EXPLAIN shows the query uses IDS_BLD_STATUS as a KEY but using FK_BLD_CONF is extremly faster.
+----+-------------+-------+------------+-------+----------------------------+----------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------------+----------------+---------+------+--------+----------+------------------------------------+
| 1 | SIMPLE | this_ | NULL | range | IDX_BLD_STATUS,FK_BLD_CONF | IDX_BLD_STATUS | 4 | NULL | 311476 | 1.09 | Using index condition; Using where |
+----+-------------+-------+------------+-------+----------------------------+----------------+---------+------+--------+----------+------------------------------------+

Adding another column to ORDER BY works well without INDEX HINT
+----+-------------+-------+------------+-------------+----------------------------+----------------------------+---------+------+------+----------+--------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+----------------------------+----------------------------+---------+------+------+----------+--------------------------------------------------------------------------+
| 1 | SIMPLE | this_ | NULL | index_merge | IDX_BLD_STATUS,FK_BLD_CONF | FK_BLD_CONF,IDX_BLD_STATUS | 8,4 | NULL | 3284 | 100.00 | Using intersect(FK_BLD_CONF,IDX_BLD_STATUS); Using where; Using filesort |
+----+-------------+-------+------------+-------------+----------------------------+----------------------------+---------+------+------+----------+--------------------------------------------------------------------------+
You can see the rows was reduce from 311476 to 3284 for one of query just by adding order by configuration Id

Is it slow for all operations you mentioned (search builds, previous build, next build, latest build, latest finished build)? And do you mean that adding a single order by configuration_id boosts all of them?

Also please let me know your QB version.

it's slow for all operations I mentioned.
We are using QB 8 latest version.
log search build:

Query_time: 39.329031 Lock_time: 0.000170 Rows_sent: 25 Rows_examined: 504110

SET timestamp=1626379523;
/* criteria query */....
log previous build:

Query_time: 8.769399 Lock_time: 0.000201 Rows_sent: 1 Rows_examined: 129942

SET timestamp=1626032582;
/* from Build where configuration.id=:configurationId and id < :id order by id desc */
log redirect to build Overview:

Query_time: 40.261337 Lock_time: 0.000134 Rows_sent: 25 Rows_examined: 520075

SET timestamp=1626397763;
/* criteria query */ select ..... order by this_.QB_ID desc limit 25;

1. Current query: 25 rows in set (43.52 sec)
/* criteria query */ 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 '%f711u%') and this_.QB_BEGIN_DATE>='2021-06-01 00:00:00' and this_.QB_BEGIN_DATE<='2021-06-17 00:00:00' order by this_.QB_ID desc limit 250, 25;

2. Adding order by configuration id: 25 rows in set (0.99 sec)
/* criteria query */ 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 '%f711u%') and this_.QB_BEGIN_DATE>='2021-06-01 00:00:00' and this_.QB_BEGIN_DATE<='2021-06-17 00:00:00' order by this_.QB_ID desc, this_.QB_CONFIGURATION_ID desc limit 250, 25;

I am not a mysql expert, can you help me understanding why adding an "order by configuration_id" can improve the performance? It will hurt the performance to my understanding? Particularly taking below query:

from Build where configuration.id=:configurationId and id < :id order by id desc

The result set is already filtered by a particular configuration id

I hope to know the reason before adding this extra option.

@robinshen Please check the below article.
I'm not an MYSQL expert also and had a hard time understanding and resolving slow query problems.
However, the below article helped me a lot.

I wasn't able to reproduce the problem locally, so it wasn't feasible to try different variations of the query. But after some Googling, I had a pretty good theory of what was happening. It was the "ORDER BY" clause. MySQL tries to be smart and decides that although using the CreateAt index might have to scan through more rows, it avoids the sorting at the end. Whereas actually, using the multi-column index leads to scanning fewer rows, which gets sorted in practically no time at all.

Now that we understand the problem, how can we coax MySQL into choosing the right index? Well, if MySQL is acting smart, we can outsmart it. What could possibly go wrong? Right?

Since the choice of the index is dictated by the "ORDER BY" clause rather than the "WHERE" clause, what if we can include that decision in the "ORDER BY" clause itself? If we change ORDER BY CreateAt to ORDER BY ChannelId, DeleteAt, CreateAt, the query result remains exactly the same. Because ChannelId and DeleteAt are equality checks. But now MySQL goes: "Aha, now I have to sort by these three columns. So I'd better use the multi-column index." And that's exactly what we want!

Adding some indexed column names to the "Order by" clause, even with the same query result, processing time and searching row count are reduced dramatically.

FYI, I'm using MySQL version 5.7.33 and QuickBuild version 8.0.44

So sorry, I don't know why also, I refer topic like Mr. absalom mentioned also.

A short brief is that Query Optimizer in MySQL selects the best index to minimize the execution time of the "SELECT" query.
However, using the "Oder By" clause within the "SELECT" query makes the Query Optimizer choose an index from the list within the "Order By" clause.
Unfortunately, that decision is not best for minimizing query execution time.

The article recommends adding another column, to help Query Optimizer choosing the best index, to the "Order By" clause.

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;

MySQL 5 is quite old. I tested on MySQL 8 with 2 million build records: the first takes about 1 second, and adding extra order for "BEGIN_DATE" costs about 4 seconds.

Please test your data on MySQL8 to see what happens.