What are the different ways of counting the number of elements in the linked table?

Let's say you have two tables: customers (customer) and the orders (order).
CREATE TABLE `customer` (
 id INT,
 name VARCHAR(255)
);
CREATE TABLE `order` (
 id INT,
 customer_id INT
);

You need to request a list of buyers, which would contain the number of orders each customer. 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 further questions:
  1. What are the pitfalls of this query?
  2. How effective is this method, if we have millions of records in both tables.
  3. What optimization opportunities exist, but to add order_count field to the order table and update it when izmeneniya with customer orders.
  4. If we bring in 100 customers to the page, how wise is it to make 100 individual requests to retrieve the number of orders for each customer on the page.
  5. Can I help indexes and how?

PS Interested in the implementation on MySQL, but on PostgreSQL if this is solved somehow better, then I too would have been interesting to see.
April 3rd 20 at 17:12
2 answers
April 3rd 20 at 17:14
Solution
SELECT customer.name, COUNT(`order`.id)
 FROM customer 
 INNER JOIN `order` ON customer.id = `order`.customer_id
 GROUP BY customer.name
Thanks for an interesting variant!
Can you tell what are its advantages? - Priscilla_Wintheiser39 commented on April 3rd 20 at 17:17
@Priscilla_Wintheiser39,

1) pitfall - the subqueries. In the General case, a million subqueries are slower than one join.
2) not effective
3) take the answer @Herbert_Green
4) unreasonable. It is reasonable to do in one request
5) the index will help to read the table in part. If the request is to read the customer by id, then you need an index on customer.id and order.customer_id

PS. Implementation for postgres and mysql for the same - charlene_Lindgr commented on April 3rd 20 at 17:20
@charlene_Lindgr, thank you for the detailed review! - Priscilla_Wintheiser39 commented on April 3rd 20 at 17:23
April 3rd 20 at 17:16
Solution
Absolutely "head":

Orders are grouped by buyer. Point.

SELECT 
`order`.customer_id,
orders_count=COUNT(*) 
FROM `order` 
GROUP BY `order`.customer_id


The output of this entity - obtained a unique list of customer "customer-sales"

Well and further - to taste:
- joins them to the entity table buyers and get something
- joins them to the buyers this entity, and depending on the terms left/inner - receive all customers with statistics, or only the real buyers with the same statistics
- use this entity in cte (the same join from another angle)

Find more questions by tags MySQLSQL