-- Create the customers table
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL
);
-- Insert 25 random customer records
INSERT INTO customers (name) VALUES
('Alice Smith'), ('Bob Johnson'), ('Charlie Brown'), ('Diana Prince'), ('Ethan Hunt'),
('Fiona Gallagher'), ('George Miller'), ('Hannah Abbott'), ('Ian Fleming'), ('Jane Austen'),
('Kevin Spacey'), ('Laura Croft'), ('Michael Jordan'), ('Nancy Drew'), ('Oliver Twist'),
('Penelope Cruz'), ('Quentin Tarantino'), ('Rachel Green'), ('Steve Rogers'), ('Tina Turner'),
('Ursula K. Le Guin'), ('Victor Hugo'), ('Wendy Darling'), ('Xavier Roberts'), ('Yvonne Craig');
-- Create the orders table
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INT,
order_date DATETIME,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);
CREATE INDEX idx_orders_customer ON orders(customer_id);
INSERT INTO orders (customer_id)
WITH RECURSIVE
row_num (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM row_num WHERE n < 1000
)
SELECT (ABS(RANDOM()) % 25) + 1
FROM row_num;
CREATE TEMP TABLE IF NOT EXISTS _benchmark (
start_time REAL
);
-- Очищаем таблицу и записываем текущее время
DELETE FROM _benchmark;
INSERT INTO _benchmark (start_time) VALUES (STRFTIME('%f', 'now'));
SELECT customers.customer_id, COUNT(order_id) AS orders_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;
SELECT
(STRFTIME('%f', 'now') - start_time) * 1000 AS 'ExecutionTime_ms'
FROM
_benchmark;
DELETE FROM _benchmark;
INSERT INTO _benchmark (start_time) VALUES (STRFTIME('%f', 'now'));
SELECT
customers.customer_id,
(SELECT COUNT(order_id) FROM orders WHERE orders.customer_id = customers.customer_id) AS orders_count
FROM customers;
SELECT
(STRFTIME('%f', 'now') - start_time) * 1000 AS 'ExecutionTime_ms2'
FROM
_benchmark;
EXPLAIN QUERY PLAN -- План для JOIN
SELECT c.customer_id, COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
EXPLAIN QUERY PLAN -- План для Подзапроса
SELECT c.customer_id,
(SELECT COUNT(o.order_id)
FROM orders o
WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;
Telegram: DB developers channel