Database Query and Performance Modernisation

[RussH]

OpenCATS still contains a number of database/query patterns inherited from the original application which are either inefficient on larger datasets, deprecated, or more complicated than necessary with modern MariaDB.

This issue tracks incremental improvements to the database layer while preserving existing OpenCATS behaviour.

The work will be split into small, independently reviewable PRs rather than implemented as one large refactor.

Database baseline

For this work:

• MariaDB 10.7 remains the known-good reference database.
• Existing CI already runs against MariaDB 10.7.
• Do not change the MariaDB version as part of this work.
• MariaDB versions newer than 10.7 have previously demonstrated behavioural/startup differences and should be investigated separately.
• Do not add MySQL compatibility work to this issue.
• Avoid deliberately introducing unnecessary MySQL incompatibility, but MariaDB behaviour is the reference for testing and optimisation.
• PHP 8.4.1 and PHP 8.5 remain the supported PHP CI targets.

---

General principles

• Preserve current OpenCATS behaviour before optimising it.
• Add regression/CI coverage before significant query changes.
• Keep PRs small and independently reviewable.
• Reuse existing OpenCATS database and DataGrid functions.
• Do not introduce a new generic query-builder abstraction.
• Do not use regex to parse or rewrite generated SQL.
• Optimise the SQL first, then add indexes matching the resulting access patterns.
• Benchmark performance changes against a realistically sized upgraded OpenCATS database.
• Use EXPLAIN / ANALYZE and slow-query evidence rather than assuming a rewrite is faster.

---

Phase 1 — Establish regression coverage

Before changing the larger queries, improve CI coverage around the behaviour we intend to preserve.

DataGrid behaviour

• Add tests for pagination.
• Add tests for total result counts.
• Add sorting tests.
• Add filtering tests.
• Add tests for filters currently using HAVING.
• Test saved-list Candidate DataGrids.
• Test DataGrid exports where applicable.

Candidate behaviour

• Test Candidate latest/recent pipeline status.
• Test submitted-to-job-order indicator.
• Test candidate attachment/presence indicators where affected by query changes.

Job Order behaviour

• Test pipeline totals.
• Test Not Contacted totals.
• Test Submitted totals.
• Test Interview totals.

Activity behaviour

• Test Candidate activity rows.
• Test Contact activity rows.
• Test mixed Candidate/Contact activity results.
• Test date filtering and ordering.

Search behaviour

Existing DatabaseSearchTest primarily validates generated SQL strings.

• Add integration tests which prove actual search result behaviour.
• Capture current AND / OR / NOT behaviour.
• Capture wildcard behaviour.
• Capture quoted-term behaviour.

These tests should exist before the search subsystem itself is modernised.

---

Phase 2 — Low-risk query and index improvements

Start with changes which do not materially restructure the existing DataGrid architecture.

Activity UNION ALL

Current ActivityDataGrid combines Candidate and Contact queries using UNION.

The two result sets are inherently separated by data_item_type, therefore duplicate elimination should not normally be necessary.

• Confirm equivalent results with CI tests.
• Replace UNION with UNION ALL.
• Compare execution plans before and after.

---

Review missing composite indexes

Evaluate the following against current master queries and a realistic database.

Do not add them solely because they improved the older PHP 5.4 installation.

Candidate latest pipeline/status

Evaluate:

(candidate_id, date_modified)

on candidate_joborder.

Current Candidate queries frequently locate a candidate's most recently modified pipeline record.

• Compare against the current individual candidate_id and date_modified indexes.
• Add only if ANALYZE demonstrates a useful improvement.

Job Order pipeline

Evaluate:

(joborder_id, status)

on candidate_joborder.

• Benchmark against the final pipeline query.

Potentially also evaluate:

(joborder_id, candidate_id)

where EXISTS/membership checks require it.

Job Order status history

Evaluate:

(joborder_id, status_to)

on candidate_joborder_status_history.

Current schema only provides a status_to index despite JobOrder queries commonly filtering by both values.

Extra fields

Evaluate:

(data_item_type, field_name, data_item_id)

on extra_field.

Current ExtraFields DataGrid joins use all three values.

Activity

Do not carry forward the old:

(data_item_type, joborder_id, data_item_id, date_created)

index unchanged.

Current ActivityDataGrid uses date_occurred.

Evaluate an index based around the current access pattern, potentially:

(data_item_type, date_occurred, activity_id)

The final definition should follow the actual optimised query plan.

---

Phase 3 — DataGrid count-query infrastructure

Introduce a backward-compatible mechanism allowing a DataGrid query to supply both its data query and its count query.

For example:

return array($dataSQL, $countSQL);

DataGrid should:

• recognise the two-query return format;
• execute the result query;
• execute the supplied count query;
• retain current string-query behaviour;
• retain the existing FOUND_ROWS() path for grids not yet migrated.

No existing grid should be converted in this infrastructure PR.

Add CI tests proving:

• legacy DataGrid query behaviour still works;
• new data/count query behaviour works;
• both produce correct pagination metadata.

This provides a gradual migration path without requiring a breaking DataGrid rewrite.

---

Phase 4 — Candidate query optimisation

Candidates are likely to provide one of the largest practical improvements on established OpenCATS installations.

Latest pipeline/status

Current Candidate DataGrid uses correlated queries similar to:

WHERE candidate_joborder.candidate_id = candidate.candidate_id
ORDER BY candidate_joborder.date_modified DESC
LIMIT 1

Initial work:

• benchmark the existing query;
• test the (candidate_id, date_modified) composite index;
• retain the existing query if indexing alone provides sufficient improvement.

Later, if required:

• replace repeated correlated latest-status lookups with one derived relation;
• evaluate ROW_NUMBER() only if it improves clarity/performance and is appropriate for the MariaDB 10.7 baseline.

Preserve exactly the same Candidate DataGrid output.

---

Replace presence joins/counts with EXISTS

Where OpenCATS only needs a boolean answer, avoid producing/aggregating unnecessary rows.

Potential candidates include:

• candidate submitted to a Job Order;
• attachment presence;
• related-record existence checks.

Use:

EXISTS (
    SELECT 1
    ...
)

where it produces a simpler and better plan.

---

Phase 5 — Job Order pipeline aggregation

Current JobOrders DataGrid repeatedly executes correlated aggregate queries for values such as:

• total pipeline;
• not contacted;
• submitted;
• interviewing.

Replace these repeated operations with grouped aggregate relations.

For example, conceptually:

SELECT
    joborder_id,
    COUNT(*) AS pipeline,
    SUM(status IN (...)) AS notContacted
FROM candidate_joborder
GROUP BY joborder_id

joined once to the JobOrder query.

• Capture existing behaviour with CI first.
• Aggregate candidate_joborder once.
• Aggregate candidate_joborder_status_history once.
• Preserve existing filtering/sorting semantics.
• Add only the indexes required by the resulting query.
• Benchmark old and new plans.

Prefer a simple derived table unless another MariaDB construct provides a clear benefit.

---

Phase 6 — Remove SQL_CALC_FOUND_ROWS

Once the two-query DataGrid infrastructure is established, migrate grids individually.

Current users include:

• Candidates
• Job Orders
• Contacts
• Companies
• Activity
• Home DataGrids
• Lists DataGrids
• Any other remaining SQL_CALC_FOUND_ROWS queries

Each migration should:

1. execute the normal result query with its LIMIT;
2. execute a separate lightweight count query;
3. preserve existing filters;
4. preserve current HAVING behaviour;
5. avoid display-only joins/calculations in the count query where possible;
6. prove identical row counts and pagination in CI.

After all callers are migrated:

• remove the FOUND_ROWS() fallback from DataGrid;
• remove comments/documentation requiring SQL_CALC_FOUND_ROWS.

---

Phase 7 — Make DataGrid filters index-friendly

Some current DataGrid filtering operates on formatted values using HAVING, for example:

HAVING DATE_FORMAT(candidate.date_modified, '%m-%d-%y') = ...

Where the filter is not genuinely aggregate-dependent:

• move filtering from HAVING to WHERE;
• filter raw datetime columns rather than formatted display values;
• retain DATE_FORMAT() only for presentation.

For a single date, prefer a range such as:

WHERE date_modified >= '2026-08-28 00:00:00'
  AND date_modified <  '2026-08-29 00:00:00'

This should allow existing datetime indexes to participate in the query.

Apply this incrementally per DataGrid.

---

Phase 8 — Page IDs before expensive display calculations

For DataGrids which remain expensive after the earlier improvements:

• identify/filter/order the primary IDs required for the current page;
• apply LIMIT to that smaller result;
• join/enrich only those rows required for display.

Ensure:

• all filters determining page membership occur before the LIMIT;
• all sorting determining page membership occurs before the LIMIT;
• ordering has a deterministic primary-key tie-breaker;
• exports remain unaffected.

Do not implement this through regex/string manipulation of generated SQL.

Each DataGrid should explicitly define its query because each grid has different joins, filters and computed columns.

---

Phase 9 — Search subsystem modernisation

DatabaseSearch.php contains a substantial custom search-language implementation built around:

• AND / OR / NOT parsing;
• quoted values;
• wildcard handling;
• SQL REGEXP;
• %LIKE%;
• custom full-text encoding.

Treat this as a separate substantial change.

First: preserve existing behaviour

• Expand integration tests.
• Benchmark current search against the upgraded production-size database.
• Identify slow search expressions from the slow-query log.

Then evaluate simplification

• Review MariaDB 10.7 FULLTEXT support.
• Evaluate appropriate FULLTEXT indexes.
• Compare existing OpenCATS semantics with MATCH() ... AGAINST().
• Determine whether some or all of the custom parser can be removed.

Do not replace the current search implementation unless its user-visible behaviour is either preserved or intentionally changed and documented.

---

Phase 10 — Simplify database writes

Several older OpenCATS code paths perform:

DELETE existing value
INSERT replacement value

Examples include settings and extra-field values.

Evaluate:

• suitable unique constraints;
• existing production databases for duplicate data;
• MariaDB INSERT ... ON DUPLICATE KEY UPDATE;
• whether atomic UPSERTs simplify the existing code and improve concurrency.

Treat this separately from DataGrid performance work.

---

Production-size performance validation

Use a copy of a genuine OpenCATS 0.9.4 production database.

• Restore a copy into a test environment.
• Upgrade it through the current OpenCATS schema migrations.
• Confirm expected behaviour after migration.
• Enable MariaDB slow-query logging.
• Capture baseline slow queries.
• Identify Candidate, JobOrder, Activity and search hotspots.
• Record EXPLAIN / ANALYZE results before changes.
• Repeat after each optimisation.
• Compare execution time, rows examined, loops, temporary tables and filesorts.
• Retain only indexes/query rewrites which demonstrate useful improvement.

CI should prove correctness.

The upgraded production-size database should prove performance.

---

Suggested implementation order

• 1. Expand database/DataGrid regression coverage
• 2. Activity UNION → UNION ALL
• 3. Benchmark and add proven composite indexes
• 4. Add backward-compatible DataGrid data/count query support
• 5. Candidate latest-status and presence-query improvements
• 6. JobOrder pipeline aggregation
• 7. Remove SQL_CALC_FOUND_ROWS grid-by-grid
• 8. Move indexable filters from formatted HAVING expressions to WHERE
• 9. IDs-first pagination for remaining expensive DataGrids
• 10. Search subsystem / FULLTEXT review
• 11. DELETE+INSERT / UPSERT simplification

Out of scope

The following should be separate issues:

• upgrading MariaDB beyond 10.7;
• investigating the known behaviour differences in later MariaDB releases;
• adding/supporting MySQL as an additional CI database;
• frontend/UI modernisation;
• major database abstraction/framework changes.