Skip to content

Commit 30a0c64

Browse files
nolanpronolanpro
authored
Merge pull request #8995 from ProcessMaker/FOUR-32470
FOUR-32470 DoqMind Speed performance on tasks API
2 parents f820258 + 4a3faa8 commit 30a0c64

2 files changed

Lines changed: 167 additions & 0 deletions

File tree

‎database/migrations/2026_08_17_000000_add_task_pagination_indexes.php‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
<?php
2+
3+
use Illuminate\Database\Migrations\Migration;
4+
use Illuminate\Support\Facades\DB;
5+
use Illuminate\Support\Facades\Schema;
6+
7+
return new class extends Migration {
8+
private const TABLE = 'process_request_tokens';
9+
10+
private const COUNT_INDEX = 'process_request_tokens_prt_completed_task_count';
11+
12+
private const PAGE_INDEX = 'process_request_tokens_prt_task_created';
13+
14+
/**
15+
* Add indexes used by completed-task pagination.
16+
*
17+
* Run this migration during a low-traffic window. Although MySQL builds the
18+
* indexes online, it can briefly wait for metadata locks on a large table.
19+
*/
20+
public function up(): void
21+
{
22+
$clauses = [];
23+
24+
if (!Schema::hasIndex(self::TABLE, self::COUNT_INDEX)) {
25+
$clauses[] = sprintf(
26+
'ADD INDEX `%s` (`status`, `is_self_service`, `element_type`, `element_name`)',
27+
self::COUNT_INDEX
28+
);
29+
}
30+
31+
if (!Schema::hasIndex(self::TABLE, self::PAGE_INDEX)) {
32+
$clauses[] = sprintf(
33+
'ADD INDEX `%s` (`is_self_service`, `created_at` DESC, `status`, `element_type`, `element_name`)',
34+
self::PAGE_INDEX
35+
);
36+
}
37+
38+
$this->alterTable($clauses);
39+
$this->refreshStatistics();
40+
}
41+
42+
/**
43+
* Remove completed-task pagination indexes.
44+
*/
45+
public function down(): void
46+
{
47+
$clauses = [];
48+
49+
if (Schema::hasIndex(self::TABLE, self::COUNT_INDEX)) {
50+
$clauses[] = sprintf('DROP INDEX `%s`', self::COUNT_INDEX);
51+
}
52+
53+
if (Schema::hasIndex(self::TABLE, self::PAGE_INDEX)) {
54+
$clauses[] = sprintf('DROP INDEX `%s`', self::PAGE_INDEX);
55+
}
56+
57+
$this->alterTable($clauses);
58+
$this->refreshStatistics();
59+
}
60+
61+
private function alterTable(array $clauses): void
62+
{
63+
if ($clauses === []) {
64+
return;
65+
}
66+
67+
DB::statement(sprintf(
68+
'ALTER TABLE `%s` %s, ALGORITHM=INPLACE, LOCK=NONE',
69+
self::TABLE,
70+
implode(', ', $clauses)
71+
));
72+
}
73+
74+
private function refreshStatistics(): void
75+
{
76+
DB::statement(sprintf('ANALYZE TABLE `%s`', self::TABLE));
77+
}
78+
};

‎tests/Feature/Migrations/AddTaskPaginationIndexesTest.php‎

Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
1+
<?php
2+
3+
namespace Tests\Feature\Migrations;
4+
5+
use Illuminate\Database\Events\QueryExecuted;
6+
use Illuminate\Support\Facades\DB;
7+
use Illuminate\Support\Facades\Schema;
8+
use Tests\TestCase;
9+
10+
class AddTaskPaginationIndexesTest extends TestCase
11+
{
12+
private const MIGRATION_PATH = 'database/migrations/2026_08_17_000000_add_task_pagination_indexes.php';
13+
14+
private const COUNT_INDEX = 'process_request_tokens_prt_completed_task_count';
15+
16+
private const PAGE_INDEX = 'process_request_tokens_prt_task_created';
17+
18+
protected $connectionsToTransact = [];
19+
20+
public function testUpCreatesBothIndexesAndRefreshesStatistics(): void
21+
{
22+
$migration = $this->migration();
23+
$migration->down();
24+
25+
try {
26+
$queries = $this->captureQueries(fn () => $migration->up());
27+
28+
$this->assertTrue(Schema::hasIndex('process_request_tokens', self::COUNT_INDEX));
29+
$this->assertTrue(Schema::hasIndex('process_request_tokens', self::PAGE_INDEX));
30+
$this->assertStatisticsWereRefreshed($queries);
31+
} finally {
32+
$migration->up();
33+
}
34+
}
35+
36+
public function testUpIsIdempotentAndStillRefreshesStatistics(): void
37+
{
38+
$migration = $this->migration();
39+
$migration->up();
40+
41+
$queries = $this->captureQueries(fn () => $migration->up());
42+
43+
$this->assertTrue(Schema::hasIndex('process_request_tokens', self::COUNT_INDEX));
44+
$this->assertTrue(Schema::hasIndex('process_request_tokens', self::PAGE_INDEX));
45+
$this->assertStatisticsWereRefreshed($queries);
46+
$this->assertFalse(collect($queries)->contains(
47+
fn (string $query) => str_starts_with($query, 'ALTER TABLE `process_request_tokens`')
48+
));
49+
}
50+
51+
public function testDownRemovesBothIndexesAndRefreshesStatistics(): void
52+
{
53+
$migration = $this->migration();
54+
$migration->up();
55+
56+
try {
57+
$queries = $this->captureQueries(fn () => $migration->down());
58+
59+
$this->assertFalse(Schema::hasIndex('process_request_tokens', self::COUNT_INDEX));
60+
$this->assertFalse(Schema::hasIndex('process_request_tokens', self::PAGE_INDEX));
61+
$this->assertStatisticsWereRefreshed($queries);
62+
} finally {
63+
$migration->up();
64+
}
65+
}
66+
67+
private function migration()
68+
{
69+
return include base_path(self::MIGRATION_PATH);
70+
}
71+
72+
private function captureQueries(callable $callback): array
73+
{
74+
$queries = [];
75+
76+
DB::listen(function (QueryExecuted $query) use (&$queries) {
77+
$queries[] = $query->sql;
78+
});
79+
80+
$callback();
81+
82+
return $queries;
83+
}
84+
85+
private function assertStatisticsWereRefreshed(array $queries): void
86+
{
87+
$this->assertContains('ANALYZE TABLE `process_request_tokens`', $queries);
88+
}
89+
}

0 commit comments

Comments
 (0)