S
S
Svyatoslav2019-12-09 21:24:24
MySQL
Svyatoslav, 2019-12-09 21:24:24

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

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 the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-12-09
@ShameQNS

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question