Optimize SQL query with multiple join condition

Question

I have two mysql tables like bellow:

table_category
-----------------
id | name | type    
1  | A    | Cloth     
2  | B    | Fashion    
3  | C    | Electronics    
4  | D    | Electronics

table_product
------------------
id | cat_cloth | cat_fashion | cat_electronics    
1  | 1         | 2           | 3    
1  | NULL      | 2           | 4

Here cat_cloth, cat_fashion, cat_electronics is ID from table_category

It is better to have another table for category type but I need a quick solution for now.

I want to get list of categories with total number of products. I wrote following query:

SELECT table_category.*, table_product.id, COUNT(table_product.id) as count
FROM table_category
LEFT JOIN table_product` ON table_category.id = table_product.cat_cloth 
    OR table_category.id = table_product.cat_fashion 
    OR table_category.id = table_product.cat_electronis
GROUP BY table_product.id
ORDER BY table_product.id ASC

Question: The sql I wrote it works but I have more then 14K categories and 50K products and the sql works very slow. I added index for cat_* ids but no improvement. My question how can I optimize this query?

I found the query takes 3-4 minutes to process the volume of data I mentioned. I want to reduce the execution time.

Best Regards

Performance questions should include EXPLAIN ANALYZE and some information about table size, index, current time performance, desire time, etc. Slow is a relative term and we need a real value to compare. MySQL Also Please read How-to-Ask — e4c5
– e4c5, Commented Dec 8, 2016 at 13:09
@Mike Andrew - can you please post some sample data on sqlfiddle.com to test the queries ? — Bernd Buffen
– Bernd Buffen, Commented Dec 8, 2016 at 13:13
@e4c5 I am not sure how to benchmark but I found the query takes 3-4 minutes to process the amount of data I mentioned. I want to reduce the execution time — Mike Andrew
– Mike Andrew, Commented Dec 8, 2016 at 13:27
so post that in your question, along with the output of explain and preferable the show create tables. — e4c5
– e4c5, Commented Dec 8, 2016 at 13:29
I have voted to close this since you have made no effort to provide required information despite two requests from me and one from @BerndBuffen — e4c5
– e4c5, Commented Dec 8, 2016 at 13:35

JosMac · Accepted Answer · 2016-12-08 14:15:44Z

1

As far as I can say every "OR" either in "ON" or "WHERE" part is very cost expensive. It will sound very stupid but I would recommend you to make 3 separate small selects combined together with UNION ALL. This we do with similar problems both in mysql and postgresql and in some cases when we got "resources exceeded" we had to do it also for bigquery. So it is very stupid and you will have more work but it certainly works and it is much quicker in producing results then many "OR"s.

answered Dec 8, 2016 at 14:15

JosMac

2,3601 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Andrew Over a year ago

yes it is expensive and I am looking for a solution for this. But UNION ALL too expensive regarding my scenario. I tried UNION ALL too before posting and it takes tame multiple by 3 then current execution time. Thanks for your reply

Collectives™ on Stack Overflow

Optimize SQL query with multiple join condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related