FOUR-32470 DoqMind Speed performance on tasks API - #8995

Issue & Reproduction Steps

DoqMind Speed performance on tasks API

Solution

GET /api/1.0/tasks with completed-task filtering took approximately 16 seconds because Laravel pagination executes an exact COUNT(*). MySQL used the low-selectivity is_self_service index, scanning roughly 4.87 million rows.

• Improves completed-task pagination performance by adding dedicated indexes for count and page queries.
• Add idx_prt_completed_task_count for the pagination count query.
• Add idx_prt_task_created for created_at DESC page retrieval.
• Preserve the existing API response, exact totals, ordering, and pagination metadata.
• Add integration tests covering index creation, idempotency, rollback, and statistics refresh.

Related Tickets & Packages

FOUR-32470

Code Review Checklist

• I have pulled this code locally and tested it on my instance, along with any associated packages.
• This code adheres to ProcessMaker Coding Guidelines.
• This code includes a unit test or an E2E test that tests its functionality, or is covered by an existing test.
• This solution fixes the bug reported in the original ticket.
• This solution does not alter the expected output of a component in a way that would break existing Processes.
• This solution does not implement any breaking changes that would invalidate documentation or cause existing Processes to fail.
• This solution has been tested with enterprise packages that rely on its functionality and does not introduce bugs in those packages.
• This code does not duplicate functionality that already exists in the framework or in ProcessMaker.
• This ticket conforms to the PRD associated with this part of ProcessMaker.