Mysql – Optimization of complicated sums and counts in related tables

countjoin;MySQLperformancePHPquery-performance

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).

Best Answer

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

The following query may help you understand this better:

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)

Related Question