SQLize | PHPize | SQLtest

-- Create the customers table CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, 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 SERIAL PRIMARY KEY, customer_id INT, order_date TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ); CREATE INDEX idx_orders_customer_id 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 -- Generate 1000 rows ) SELECT FLOOR(RANDOM() * 25)+1 FROM row_num; EXPLAIN ANALYZE 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 ANALYZE 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;