Какие существуют способы подсчета количества элементов в связанной таблице?

Question

[Святослав]

Допустим, имеются две таблицы: покупатели (customer) и заказы (order).

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

Необходимо запросить список покупателей, который бы содержал количество заказов каждого покупателя. Способ, который я знаю — это использовать вложенный подзапрос.

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

Этот запрос решает поставленную задачу, но у меня возникают дополнительные вопросы:

1. Какие подводные камни у этого запроса?
   
2. Насколько эффективен этот способ, если у нас миллионы записей в обоих таблицах.
   
3. Какие варианты оптимизации существуют, кроме как добавить поле order_count в таблицу order и актуализировать ее при измененияз с заказами покупателя.
   
4. Если мы выводим по 100 покупателей на страницу, насколько разумно сделать 100 отдельных запросов на получение количества заказов для каждого покупателя на странице.
   
5. Могут ли чем-то помочь индексы и как именно?
   

P. S. Интересует реализация на MySQL, но если на PostgreSQL это решается как-то лучше, то мне бы тоже было бы интересно посмотреть.

Answer 1

[Константин Цветков]

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

Comments

[Святослав]

Спасибо за интересный вариант!
А можете сказать, в чем его преимущества?

[Илья]

Святослав,

1) подводный камень - подзапросы. В общем случае миллион подзапросов медленнее чем один джоин.
2) не эффективен
3) взять ответ Константин Цветков
4) неразумно. Разумно делать одним запросом
5) индекс поможет читать таблицы частично. Если запрос будет вычитывать клиентов по id, то вам нужен индекс customer.id и order.customer_id

PS. Реализация для postgres и для mysql одинаковая

[Святослав]

Илья, спасибо за развернутый комментарий!

Answer 2

[d-stream]

Совершенно "в лоб":

Группируем заказы по покупателю. Точка.

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

На выходе этой сущности - получаем уникальный по кастомеру список "покупатель-число покупок"

Ну а дальше - по вкусу:
- join'им к этой сущности таблицу покупателей и получаем нечто,
- join'им к покупателям эту сущность и в зависимости от условия left/inner - получаем всех покупателей со статистикой или же только реальных покупателей с этой же статистикой
- используем эту сущность в cte (тот же join с другого ракурса)