Voer het volledige voorbeeld in één nieuwe sessie uit in een aparte MySQL- of MariaDB-testdatabase, nooit in productie. De tijdelijke tabel verdwijnt wanneer je de sessie sluit. Dit is geen SQL Server-script.
Bekijk het volledige SQL-voorbeeld met testdata
-- Synthetic demonstration for a NEW session in a separate MySQL/MariaDB test database.
-- Never run this in production. Requires CREATE TEMPORARY TABLES permission.
-- Stop on the first error. Do not use a client option that continues after errors.
-- The table exists only in this session. Close the session before rerunning.
-- No measured speed improvement is claimed. EXPLAIN reports estimates, not timings.
CREATE TEMPORARY TABLE cg_query_demo_orders (
id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL,
created_at DATETIME NOT NULL
);
INSERT INTO cg_query_demo_orders (id, customer_id, created_at) VALUES
(1, 42, '2026-08-31 23:59:59'),
(2, 42, '2026-09-01 00:00:00'),
(3, 42, '2026-09-01 12:00:00'),
(4, 42, '2026-09-01 12:00:00'),
(5, 42, '2026-09-02 00:00:00'),
(6, 7, '2026-09-01 12:00:00'),
(7, 42, '2026-09-01 23:59:59'),
(8, 42, '2026-09-02 00:00:01');
-- Baseline: inspect the plan and return the actual rows.
EXPLAIN SELECT id, customer_id, created_at
FROM cg_query_demo_orders
WHERE customer_id = 42
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;
SELECT id, customer_id, created_at
FROM cg_query_demo_orders
WHERE customer_id = 42
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;
-- Candidate index, not a recommendation to add this index to production blindly.
CREATE INDEX cg_query_demo_customer_created_id
ON cg_query_demo_orders (customer_id, created_at, id);
-- Same SELECT after the index. Expected IDs before and after: 7, 4, 3, 2.
-- A table scan on eight rows can be a valid optimiser decision.
EXPLAIN SELECT id, customer_id, created_at
FROM cg_query_demo_orders
WHERE customer_id = 42
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;
SELECT id, customer_id, created_at
FROM cg_query_demo_orders
WHERE customer_id = 42
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC;
Geen gemeten klantresultaat of gegarandeerde snelheidswinst. De automatische regressietest controleert resultaatgelijkheid op SQLite, niet het uitvoeringsplan of de snelheid van MySQL, MariaDB of SQL Server.
