Benchmarks
Measured performance of the grid's hot paths against a 10,000,000-row table on ElyraSQL 1.4.7. The takeaway: interactive operations (paging, sorting) are ~30 ms regardless of table size, and the expensive analytical operations (row count, aggregates, facets, grouping) are opt-in and cached per filter — you pay for them once, then paging and sorting reuse the cached values.
Dataset
The sales table from bench/schema.sql: 10M rows,
columns id, order_ts, store_id, region, category, sku, customer_id, status, qty, unit_price, discount, revenue, margin. Secondary indexes on revenue,
region, status, plus a compound (revenue, id) for sorted-at-scale reads.
Interactive operations (what the user feels)
Measured through the HTTP grid endpoint (best of 3, includes PHP + HTTP + JSON overhead — not just the query):
| Operation | Cost |
|---|---|
| Viewport page (20 rows, no meta) | ~30 ms |
| Viewport page within a filter | ~30 ms |
Sort by an indexed column (revenue asc/desc) |
~29 ms |
Deep page within a filter (skip 100,000) |
~306 ms |
Sorting is index-backed: the grid emits ORDER BY <col> <dir>, <key> <dir>,
which the engine serves as an index top-N from a compound (<col>, <key>)
index. Sorting a 10M-row table by a secondary column dropped from ~6 s to ~30 ms
once ElyraSQL 1.4.7 landed index top-N and a compound index was added.
Analytical operations (opt-in, cached per filter)
Raw single-query cost (direct SQL, one pass over 10M rows). These run only when the relevant feature is enabled, and their results are cached per filter — changing page or sort does not recompute them:
| Operation | Cost |
|---|---|
COUNT(*) (total, whole table) |
~1.7 s |
COUNT(*) with a selective filter |
~0.5 s |
GROUP BY region (+ sum/avg) |
~4 s |
Facet value list (single-pass FACET) |
one pass |
| Footer aggregates (5 functions) | ~2 s |
Cost model in practice: the first load of a given filter computes the total and footer aggregates once. Every subsequent page/sort within that filter is just the ~30 ms viewport query. Facets are lazier still — computed only when a filter dropdown opens, then cached until the filter changes.
Export
Streamed in bounded key-range windows (constant memory, single scan per window):
| Metric | Value |
|---|---|
| Rows | 1,666,667 (full region = Nord) |
| Time (CLI, single unbuffered pass) | ~5.5 s |
| Time (HTTP, windowed) | ~52 s |
| Peak PHP memory | ~24–90 MB (flat, independent of row count) |
Method & caveats
- Numbers are best of 3 on a warm engine; treat them as order-of-magnitude, not guarantees. Hardware, indexes, and ElyraSQL version all matter.
- Interactive figures include the full PHP + HTTP + JSON round-trip, so they reflect what a client actually experiences, not just the SQL.
- Analytical figures are raw single-query costs; in a live client they are paid once per filter and cached, so they do not affect paging/sorting latency.
- Indexing matters: add a compound
(sortColumn, keyColumn)index on any column you sort at scale, and index selective filter columns. Parameterized filters benefit most from indexes that the engine can use for the bound value.
Reproduce
# 1. Load the dataset into ElyraSQL (db `elyra`)
mysql -h <host> -P <port> -u root -p elyra < bench/schema.sql
# 2. Run the timing harness
bash bench/run.sh
# or: npm run bench
See bench/ for the schema generator and the query harness.