MariaDB Tutorial / Improve Date Filters, Sorting, and Pagination Without Changing Results

Query improvement preserves the intended rows while aligning filters, ordering and pagination with suitable access paths. Express time boundaries and unique tie-breakers clearly, compare OFFSET with keyset navigation, and verify any performance benefit using actual plans and representative data.

Verify result equivalence before claiming lower cost

A faster query that drops boundary timestamps or tied rows is not an equivalent improvement. Compare returned primary keys and sequence first, then plans, work and latency. Removing a requirement to reduce cost changes the query’s meaning. Connect these changes with the ordering/keyset boundaries in Sort and Paginate Results with ORDER BY and LIMIT and the before/after plan comparison in Read EXPLAIN and Compare Plans Before and After an Index.

Check the MariaDB documentation for the syntax and conditions used here: building the best index for a given select, sargable date and year.

Prepare the date-ordered fixture and index

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c41_customers, c41_products, c41_orders, c41_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c41_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c41_order_customer FOREIGN KEY (customer_id)
  REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c41_item_order FOREIGN KEY (order_id)
  REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
  REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c41_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);

The four orders can be compared by timestamp and identifier. A full scan remains reasonable for such a small table. This fixture establishes correct rows and boundaries; larger performance effects need representative data.

Compare a date function with an explicit range

SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;

Both queries return order 101. The range states the complete day, including times and fractional seconds. From MariaDB 11.1, supported DATE and YEAR predicates can be transformed into index ranges, so a function on a column does not universally prevent index use.

An explicit range can make cross-version intent easy to read, but it does not by itself prove superior speed. Compare chosen keys and ranges with EXPLAIN. Formatting a date as text and comparing that output can mix display and search concerns, so inspect types too.

Use both timestamp and identifier for the next page

SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
 OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;

The first descending page is 104, 103. With order 103’s timestamp and identifier as the cursor, the next page is 102, 101. The identifier condition prevents tied timestamps from being skipped. Changing sort direction requires reviewing the comparison directions as well.

Keyset navigation can avoid deep offsets, but it is not the same interface as jumping directly to any numbered page. Concurrent modifications can still change a list between requests. Review the ordering and LIMIT guide and decide whether the business needs a fixed snapshot.

Test an equal-time page boundary using this rollbackable change.

START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
   OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;

The first result is 104; the next is 103 at the same timestamp. A timestamp-only boundary could skip it. After ROLLBACK, verify that order 104 has its original timestamp.

Review projection, selectivity and the actual search pattern

SELECT * can unnecessarily transfer large text values and increase memory or row-access cost. Reducing selected columns differs from reducing qualifying rows. A plan can show whether the required values are available through a covering access path.

For ordinary B-tree access, a prefix LIKE pattern and a leading-wildcard contains pattern have different index possibilities. Replacing contains with prefix search may appear faster while silently changing results. Choose the intended pattern and collation, then assess a suitable search strategy.

Record comparison conditions and review neighboring use cases

Keep both query versions, returned keys, server version, index definitions, row counts and distribution. Distinguish cache effects from structural improvement, and do not force an index merely to match an expected plan. After changing a query, review related sort and pagination requirements that may share its access path.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;