# Optimization of complicated sums and counts in related tables

Posted on

### Question :

I have a `categories` table, which have two columns, `id` and `name`.

I have a `types` table, which have three columns, `id`, `category_id` and `points`.

I have a `products` table, which have three columns, `id`, `type_id` and `customer_id`.

This means each product has a type, and each type has a category. One category has many types, one type has many products.

I am using MySQL and PHP.

From PHP, given a `customer_id`, I want to know how much points that customer has in each category separately (and display a table to the user, for example).

My solution:
I would first do a query to find what are all category ids, then for each `category_id` I would find all types inside that category (that is, all types with the given `category_id`); then for each `type_id` I would count how many products have both that `type_id` and the given `customer_id`, and I would multiply that amount for the points of the given `type_id`. Adding all the results for each `type_id`, I have in the end the total points the given customer has on that `category_id`. And as I said, I would have to do this for each `category_id`. My solution will work, but seems to me that it would be VERY slow. I am looking for advice on how to solve this problem in a better way – with less queries, or faster queries, or both. The way it is now, it can potentially do as many queries as the amount of categories.

Example:

``````/* TABLE: categories */

name           id
A              1
B              2
C              3

/* TABLE: types */

points         id          category_id
1000             1                1
500              2                1
200              3                3
50               4                2
20               5                3
1                6                3

/* TABLE: products */

id           type_id          customer_id
1               2                   13
2               4                   13
3               5                   13
4               6                   13
5               5                   13
6               3                   13
7               4                   13
8               5                   13
9               6                   13
``````

If the given customer_id is 13, I want get something like the following:

500 total points in category A
100 total points in category B
262 total points in category C

1. What is a good way to do this without changing how my database is structured?

2. Is there a good way to change my database design to allow a better way to do this? (of course I have other fields in the tables that I omitted for simplicity).

Your question shows you are thinking in a procedural way, however you need to think in a “set-based” way.

``````SELECT p.customer_id
, c.name
, sum(t.points)
FROM products p
INNER JOIN types t ON p.type_id = t.id
INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
, c.name;
``````

Essentially, this should show you a list of customer_id, category name, and the total points each customer has in each category. It accomplishes this through the use of an aggregate function (`SUM`) along with the `GROUP BY` clause.

(please take a look at my answer here for information about naming your `ID` columns)