What are the ways to count the number of elements in a linked table?

Question

MySQL

Suppose there are two tables: buyers (customer) and orders (order).

CREATE TABLE `customer` (
    id   INT,
    name VARCHAR(255)
);
CREATE TABLE `order` (
    id          INT,
    customer_id INT
);

It is necessary to request a list of buyers, which would contain the number of orders of each buyer. The way I know is to use a nested subquery.

SELECT customer.name,
       (SELECT COUNT(*) 
          FROM `order` 
         WHERE `order`.customer_id = customer.id) AS order_count
  FROM customer;

This query solves the problem, but I have additional questions:

1. What are the pitfalls of this request?
   
2. How efficient is this method if we have millions of records in both tables.
   
3. What optimization options exist, other than adding the order_count field to the order table and updating it when it changes with buyer orders.
   
4. If we are displaying 100 customers per page, how reasonable is it to make 100 separate requests for the number of orders for each customer on the page.
   
5. Can indexes help in some way and how exactly?
   

PS I'm interested in the implementation on MySQL, but if PostgreSQL solves this somehow better, then I would also be interested to see it.

Answer 1

SELECT customer.name, COUNT(`order`.id)
  FROM customer 
    INNER JOIN  `order` ON customer.id = `order`.customer_id
  GROUP BY customer.name