ORM Query Shapes, Pagination, N+1, Bulk Operations, and Index Visibility

Learning outcomes

ServiceHub adopts an Object-Relational Mapper (ORM) so developers can work in application objects. Response time later climbs from 40 ms to 900 ms, even though no one “changed SQL.” The ORM did: one endpoint now loads 100 tickets and lazily queries each customer, and a report paginates with a six-digit OFFSET. An ORM removes repetitive mapping code; it does not remove MariaDB’s optimizer, indexes, lock duration, network round trips, or transaction semantics.

1. Create the query-shape dataset

DROP DATABASE IF EXISTS servicehub19_l4;CREATE DATABASE servicehub19_l4 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub19_l4;CREATE TABLE customers (  customer_id BIGINT PRIMARY KEY,  display_name VARCHAR(120) NOT NULL) ENGINE=InnoDB;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  updated_at DATETIME(6) NOT NULL,  subject VARCHAR(180) NOT NULL,  INDEX ix_status_updated_id(status, updated_at, ticket_id),  INDEX ix_customer(customer_id),  CONSTRAINT fk_ticket_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id)) ENGINE=InnoDB;INSERT INTO customers VALUES (1,'Ada'),(2,'Grace'),(3,'Linus'),(4,'Mina');INSERT INTO tickets(customer_id,status,updated_at,subject)WITH RECURSIVE seq AS (  SELECT 1 n UNION ALL SELECT n+1 FROM seq WHERE n<1000)SELECT MOD(n,4)+1,       IF(MOD(n,5)=0,'closed','open'),       TIMESTAMP('2026-08-20 00:00:00') + INTERVAL n SECOND,       CONCAT('Ticket ',n)FROM seq;DROP USER IF EXISTS 'svc19_orm'@'127.0.0.1';CREATE USER 'svc19_orm'@'127.0.0.1' IDENTIFIED BY 'local-orm-lab';GRANT SELECT, INSERT, UPDATE, DELETE ON servicehub19_l4.* TO 'svc19_orm'@'127.0.0.1';

2. Turn on ORM SQL logging before guessing

npm install sequelize mariadb

const { Sequelize, DataTypes } = require('sequelize');let queryCount = 0;const sequelize = new Sequelize('servicehub19_l4', process.env.DB_USER || 'svc19_orm', process.env.DB_PASSWORD, {  host: '127.0.0.1',  dialect: 'mariadb',  logging: sql => { queryCount++; console.log(sql); }});

Logging proves what the ORM asks MariaDB to execute. It does not prove cost or row counts by itself; copy representative SQL into EXPLAIN/ANALYZE on the target server and correlate with Chapter 17 Performance Schema/slow-log evidence.

3. N+1: one object loop becomes many round trips

const tickets = await Ticket.findAll({  where: { status: 'open' },  limit: 100,  order: [['updated_at','DESC'],['ticket_id','DESC']]});for (const t of tickets) {  t.customer = await Customer.findByPk(t.customer_id);}console.log({ queryCount });

The first query loads tickets; the loop can add one customer query per ticket. Even fast indexed point lookups become expensive when multiplied by network round trips and connection contention. Repair the shape by eager-loading/joining the required customer columns in one bounded query, then confirm the actual generated SQL.

queryCount = 0;const tickets = await Ticket.findAll({  where: { status: 'open' },  include: [{ model: Customer, attributes: ['customer_id','display_name'] }],  limit: 100,  order: [['updated_at','DESC'],['ticket_id','DESC']]});console.log({ queryCount });

4. OFFSET vs keyset pagination

EXPLAINSELECT ticket_id, customer_id, updated_at, subjectFROM ticketsWHERE status='open'ORDER BY updated_at DESC, ticket_id DESCLIMIT 50 OFFSET 800;-- Keyset/cursor page after the last item from the previous page:EXPLAINSELECT ticket_id, customer_id, updated_at, subjectFROM ticketsWHERE status='open'  AND (updated_at, ticket_id) < ('2026-08-20 00:14:00', 840)ORDER BY updated_at DESC, ticket_id DESCLIMIT 50;

OFFSET is simple and useful for shallow navigation, but deep pages still require the server to find/skip earlier ordered rows. Keyset pagination uses the last deterministic sort key as a cursor and can keep work proportional to page size when the index supports the predicate/order. It sacrifices arbitrary “jump to page 17,432” semantics and requires a stable ordering contract.

5. Bulk operations and transaction boundaries

ORM convenience loops often issue one INSERT/UPDATE per object. Prefer the ORM’s documented bulk APIs or a bounded multi-row statement when semantics allow it. But “one giant bulk transaction” is not automatically better: it can create large undo/redo/binlog volume, hold locks longer, increase Galera write-set cost, and make retry/recovery expensive. Chapter 16’s chunking discipline applies at the ORM layer too.

const batch = rows.slice(0, 500).map(r => ({  customer_id: r.customerId,  status: r.status,  updated_at: r.updatedAt,  subject: r.subject}));await Ticket.bulkCreate(batch, { validate: true });

6. Wrong approach: trust model indexes instead of the live schema

ORM model declarations, migration files and the live MariaDB schema can drift. A model annotation is not proof that the index exists or that MariaDB uses it. Verify with SHOW CREATE TABLE, SHOW INDEX and EXPLAIN. If the ORM generates a function/cast around an indexed column, changes collation, or uses a different predicate order, the access path may differ from what the model author expected.

SHOW CREATE TABLE servicehub19_l4.tickets;SHOW INDEX FROM servicehub19_l4.tickets;EXPLAIN SELECT ticket_id, updated_atFROM servicehub19_l4.ticketsWHERE status='open'ORDER BY updated_at DESC, ticket_id DESCLIMIT 50;

7. Reproducible lab and cleanup

Run the N+1 and eager versions against the same dataset and record: ORM query count, end-to-end duration, database statement count, generated SQL, and the plan for the dominant SQL. Then compare OFFSET depths 0, 500 and 800 locally. Do not generalize the tiny dataset’s timings to production; the lesson is the measurement method and query-shape mechanism.

DROP USER IF EXISTS 'svc19_orm'@'127.0.0.1';DROP DATABASE IF EXISTS servicehub19_l4;

Production judgment and bridge to Lesson 5

Keep the ORM where it improves maintainability, but establish an escape hatch for critical SQL and a review process for generated query shapes. Query count, rows examined/returned, plan changes and transaction duration belong in application/database observability. Lesson 5 completes the application contract by coordinating schema evolution, deployment health and endpoint failover so old and new application versions can safely overlap.

ORM evidence workflow: translate object operations back into SQL and access paths

An object-relational mapper can improve application structure while still issuing inefficient SQL. The operational rule is simple: when performance or correctness matters, capture the generated SQL, parameters or parameter classes, transaction boundaries, and query count for one representative request. Then reason about MariaDB using the same tools as hand-written SQL—indexes, EXPLAIN/ANALYZE where safe, statement digests, lock evidence, and observed latency.

The N+1 problem is a query-count multiplication problem. Loading 100 parent rows and lazily fetching one child collection per parent can turn one request into 101 round trips even when every individual statement is indexed. Eager loading can repair the round-trip count but may create a wide join with duplicated parent data. A third option is bounded prefetching in a small number of queries. The right choice is measured against row counts, payload size, cardinality, and application memory—not selected from an ORM slogan.

Pagination also exposes database semantics. Large OFFSET values usually require MariaDB to find and discard earlier rows, and concurrent inserts/deletes can make page boundaries unstable. Keyset pagination uses a deterministic ordered key—often a timestamp plus a unique tie-breaker—and asks for rows after the last seen key. That shifts the design requirement to a matching index and stable ordering. It is not interchangeable with “page 37” navigation, so the API contract matters.

Bulk APIs deserve the same scrutiny. Verify whether the ORM emits one statement per row, a multi-row insert, connector batch execution, or database-specific upsert syntax; check transaction size and returned generated identifiers. If a framework hides SQL, enable its SQL logging in a sanitized test environment and treat the emitted SQL as part of the deployment artifact that regression tests can inspect.

Authoritative references

Primary references are current MariaDB documentation or official connector source; verify the target server/connector version before relying on defaults or option behavior.

• MariaDB EXPLAIN
• MariaDB indexes guide
• MariaDB Connector/Node.js official source repository
• Sequelize documentation (external, free/open)