GSoC 2026 Final Work Product — Query Optimization and Performance Benchmarking

Project information

• Contributor: Yishar Piero Nieto Barrientos
• GitHub: iAmMazapan
• Organization: omegaUp
• Repository: omegaup/omegaup
• Project: Query Optimization and Performance Benchmarking
• Work period covered: May 25–August 24, 2026
• Last updated: August 24, 2026

Project overview

This project focused on improving omegaUp's database performance and detecting inefficient SQL queries before they reach production.

The original proposal had three deliverables:

1. Find and optimize inefficient queries using MySQL general_log and EXPLAIN.
2. Turn the EXPLAIN report into a CI check with a versioned allowlist.
3. Add execution-time benchmarks for critical queries.

After reviewing the timeline with my mentors, we decided to focus on the first two parts. Performance benchmarking was left for later because it did not fit within the available time.

Most of my work went into query optimization, with eleven pull requests merged. The EXPLAIN work now includes query normalization, stable identities, Query Family grouping, reliable general_log cleanup, a versioned allowlist, and an initial reviewed baseline. The final comparison and CI failure changes have also been submitted and are under review.

Summary of results

Across the project and the follow-up work after the final submission, I opened 19 pull requests: 17 were merged and 2 are currently under review. I also created 22 issues to document the problems, proposed changes, and follow-up work.

Work completed

1. SQL query optimizations

For each optimization, I located the inefficient query, reviewed its execution plan, changed its indexes or SQL structure, and checked that its behavior did not change.

The investigation for the proposed composite index on Assignments was also tracked in #9927. The issue is closed and has no separate authored PR in this work-product list.

2. Foundations for SQL regression detection

The repository already captured SQL statements from general_log, ran EXPLAIN, and generated inefficient_queries.csv. The output was not stable enough for an allowlist, and new inefficient queries did not make CI fail.

I completed or submitted the following foundation work:

The intended data flow is:

PHP tests
   ↓
MySQL general_log
   ↓
EXPLAIN for captured queries
   ↓
Normalization + stable (query, table) identity
   ↓
Known allowlist entry ── yes ──> CI passes
                     └── no  ──> new regression; CI fails

Query Family adds a broader representation to make related CSV rows easier to inspect:

IN (?)
IN (?, ?)
IN (?, ?, ?)
        ↓
Query Family: IN (?)

This is only used for grouping and sorting. The identity remains (Normalized Query, Table) so different EXPLAIN results are not hidden.

Testing and validation

• Each merged optimization went through omegaUp's pull-request review and CI process. SQL changes were also analyzed with MySQL EXPLAIN and the relevant existing tests.
• stuff/process_mysql_explain_logs_test.py now has 28 focused tests for normalization, Query Family, stable identities, deduplication, allowlist validation, comparison, and exit codes.
• The first CI run with failure enabled found three known query-table pairs missing from the baseline. After reviewing and adding them, the PHP and SQL job passed with no new inefficient queries.
• The general_log cleanup change passes Bash syntax validation, git diff --check, and omegaUp's official lint command.

Current state and remaining work

I divided the remaining EXPLAIN work into small public issues and continued implementing them after the final submission.

These changes need to be integrated in order. The comparison PR is currently ready for review, while the CI failure PR depends on it.

Performance benchmarking remains a later phase. It would add a larger synthetic dataset, repeated query execution, average/p95/p99 measurements, reviewed thresholds, and a separate CI job.

Differences from the original proposal

The proposal originally divided the effort between query optimization (40%), the EXPLAIN check (30%), and performance benchmarks (30%). In practice, investigating and validating production queries took more time than expected. After discussing it with my mentors, we prioritized the first two parts.

This resulted in eleven merged query optimizations and a nearly complete EXPLAIN regression check. The allowlist and initial baseline are merged, while the final comparison and CI failure mechanism are under review. Performance benchmarking was deferred. The tables above show which work is merged, under review, or still planned.

Challenges and lessons learned

• An index is not automatically the right solution for every full scan. Table size, selectivity, join order, and query semantics must be considered before changing a query.
• A single SQL statement can produce several EXPLAIN rows. Treating only the normalized SQL text as its identity can silently discard a problem reported for a second table.
• A CI allowlist needs deterministic identities and a reviewed baseline before it can safely block regressions.
• The regression-detection changes have to be integrated sequentially, so some completed changes were still waiting for review at the final-submission date.
• Small, focused pull requests made it easier to validate behavior, receive mentor feedback, and adjust the implementation without combining unrelated risks.

Continuing work after the final submission

This report keeps the original final-submission date of August 24, 2026. After that submission, I completed the allowlist format and baseline, then submitted the comparison and CI failure changes. The remaining work is to cover special EXPLAIN cases and document how to review failures and maintain the allowlist.

Relevant code

The main areas changed or studied during this project are:

• frontend/database/ — database schema and index migrations.
• frontend/server/src/DAO/ — SQL queries used by omegaUp's data-access layer.
• stuff/process_mysql_explain_logs.py — EXPLAIN analysis and CSV generation.
• stuff/process_mysql_explain_logs_test.py — focused tests for detector logic.
• stuff/mysql_types.sh — execution flow that captures and processes queries.

All code submitted as project work is linked through the pull-request tables above. Work completed before the GSoC coding period, including proposal references #9648, #9666, and #9674, is context only and is not counted among the results reported here.

Acknowledgements

Thank you to my omegaUp mentors and maintainers for their guidance, reviews, and technical feedback throughout GSoC 2026.