Exécutez l’exemple complet dans une nouvelle session sur une base de test MySQL ou MariaDB distincte, jamais en production. La table temporaire disparaît à la fermeture de la session. Ce n’est pas un script SQL Server.
Voir l’exemple SQL complet avec les données de test
-- 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;
Aucun résultat client mesuré ni gain de vitesse garanti. Le test de régression automatique vérifie l’égalité des résultats sur SQLite, pas le plan d’exécution ni la vitesse de MySQL, MariaDB ou SQL Server.
