SQLize | PHPize | SQLtest

-- Create the customers table CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL ); -- Set customers and orders amount (do not set these values over ~1kk) SET @customer_amount = 25, @order_amount = 1000; -- Adjust session settings SET SESSION cte_max_recursion_depth = GREATEST(@customer_amount, @order_amount); -- Create customers INSERT INTO customers (customer_id, name) WITH RECURSIVE cte (num) AS (SELECT 1 UNION ALL SELECT NUM + 1 FROM cte WHERE num < @customer_amount) SELECT num, CONCAT('Customer No. ', num) FROM cte; ; -- 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 orders INSERT INTO orders (customer_id) WITH RECURSIVE cte (num) AS (SELECT 1 UNION ALL SELECT NUM + 1 FROM cte WHERE num < @order_amount) SELECT CEIL(RAND() * @customer_amount) FROM cte; 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;