How to count number of rows which is related to another table

Question

I have two tables:

PRODUCT

product_id          int(10)          UNSIGNED  AUTO_INCREMENT
product_category    int(10)          UNSIGNED 
product_title       varchar(255)

Column product_category is a foreign key related to category_id. Here is some data:

category_id    category_title
-----------    --------------
          3    Cellphone
          4    Motherboard
          5    Monitor

product_id    product_category    product_title
----------    ----------------    -------------
         3    3                   Samsung Galaxy SIII
         4    3                   Apple iPhone 5
         5    3                   HTC One X

How I can fetch all categories with the count of products?

category_id    category_title    products_count
-----------    --------------    --------------
          3    Cellphone         3
          4    Motherboard       9
          5    Monitor           7

I used this query:

SELECT 
    `category_id` AS  `id`,
    `category_title` AS  `title`,
    COUNT(  `product_id` ) AS  `count` 

FROM  `ws_shop_category` 
    LEFT OUTER JOIN  `ws_shop_product`
        ON  `product_category` =  `category_id` 

GROUP BY  `category_id` 
ORDER BY  `title` ASC

But it takes too long: ( 254 total, Query took 4.4019 sec). How can I make this query better?

DESC

Adding DESC before the query, give me this result:

id  select_type table               type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      ws_shop_category    ALL     NULL            NULL    NULL    NULL    255     Using temporary; Using filesort
1   SIMPLE      ws_shop_product     ALL     NULL            NULL    NULL    NULL    14320

SHOW CREATE TABLE

CREATE TABLE `ws_shop_product` (
 `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `product_category` int(10) unsigned DEFAULT NULL,
 `product_title` varchar(255) COLLATE utf8_general_ci DEFAULT NULL,
 PRIMARY KEY (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14499 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `ws_shop_category` (
 `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `category_title` varchar(255) COLLATE utf8_general_ci DEFAULT NULL,
 PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=260 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Why you are using LEFT OUTER JOIN and not INNER JOIN? that would obviously slows down the query. not to mentioned it would count even those who have no match. — Edper, Jun 08 '13 at 07:33
How have you defined the indexes on your tables? Slow queries often indicate that no index is used. You can see the query plan by adding `DESC ` at the start of the query and executing it. — Erik Schierboom, Jun 08 '13 at 07:33
@Edper Yes, inner join gave me a fast result, but I used left outer join for categories with no products (count = 0). — , Jun 08 '13 at 07:41
@ErikSchierboom I've added the result of `DESC` before my query. — , Jun 08 '13 at 07:45
@Edper the problem is the total lack of indexes, not of `LEFT JOIN`. If left join is needed, it should be used. With only 250 rows returned, it shouldn't make a difference. — ypercubeᵀᴹ, Jun 08 '13 at 07:49
@user2450111 Ah yes, that indicates that no indexes are used. I'll post and answer soon. — Erik Schierboom, Jun 08 '13 at 07:49

score 2 · Accepted Answer · answered Jun 08 '13 at 07:52

2

Your table does not define any indexes. It is easily remedied though, by adding the indexes using the following statememts:

ALTER TABLE `product` ADD INDEX `product_category` (`product_category`);
ALTER TABLE `category` ADD PRIMARY KEY(category_id);

Now if you run your query again, the DESC should show you that the query uses keys and should be much faster.

answered Jun 08 '13 at 07:52

Erik Schierboom

16,301
10
64
81

the `category` table is already have `category_id` as `primary key`. I add the first index, and my query, boom. Thanks a lot. One question: all foreign key need to be an index? – Jun 08 '13 at 07:56
@user2450111 It would help if you added the `SHOW CREATE TABLE category;` output (and same for `product` table) in the question. Then we would know all constraints, indexes and engine used. Your EXPLAIN plan does not show any index defined in `category`. – ypercubeᵀᴹ Jun 08 '13 at 07:59
@ypercube Thanks for your comment, I've added it to my post :) – Jun 08 '13 at 08:07
@user2450111 Not all foreign keys need to be an index (unless you want to enforce the values to be valid), but it helps a great deal when joining. – Erik Schierboom Jun 08 '13 at 08:58

David Houde · Answer 2 · 2013-06-08T08:03:05.653

0

SELECT ws_shop_category.*, count(ws_shop_product.product_category) as products_count        
from ws_shop_category
left join ws_shop_product
on (ws_shop_category.category_id = ws_shop_product.product_category)
group by
    ws_shop_category.category_id
order by  
    ws_shop_category.category_title asc

edited Jun 08 '13 at 08:03

answered Jun 08 '13 at 07:37

David Houde

4,835
1
20
29

1

What is this supposed to do? – ypercubeᵀᴹ Jun 08 '13 at 07:56
Show all categories, and count how many products exist in each category. Am I missing something? – David Houde Jun 08 '13 at 08:03
Now that you edited, it's the same as the OP's query. The problem is efficiency though, he already has the query. – ypercubeᵀᴹ Jun 08 '13 at 08:17

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

If you require high performance, I recommend storing number of products in a category as a column to product_category or in a separate table. You can update relevant counters on every product insert/delete (manually or using triggers) or you can use a scheduled job to update all the counters every N minutes.

You can find example of using triggers here: Speeding up row counting in MySQL

edited May 23 '17 at 11:43

Community

1
1

answered Jun 08 '13 at 08:15

Sami Korhonen

1,254
8
17

How to count number of rows which is related to another table

CATEGORY

PRODUCT

DESC

SHOW CREATE TABLE

3 Answers3