SQLize | PHPize | SQLtest

-- Create the customers table CREATE TABLE customers ( customer_id INT AUTO_INCREMENT 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 INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_date DATETIME, INDEX(customer_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ); -- Generate 1000 orders 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 FLOOR(RAND() * 25)+1 FROM row_num; SET profiling = 1; SELECT customers.customer_id, COUNT(order_id) AS orders_count FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id; SELECT customers.customer_id, ( SELECT COUNT(order_id) FROM orders WHERE orders.customer_id = customers.customer_id ) AS orders_count FROM customers; SHOW PROFILES; 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;