Let's say you have two tables: customers (customer) and the orders (order).
CREATE TABLE `customer` (
CREATE TABLE `order` (
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.
WHERE `order`.customer_id = customer.id) AS order_count
This query solves the problem, but I have further questions:
- What are the pitfalls of this query?
- How effective is this method, if we have millions of records in both tables.
- What optimization opportunities exist, but to add order_count field to the order table and update it when izmeneniya with customer orders.
- 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.
- 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.