MySQL - Multiple JOIN on Same Table

Question

I have table foo1 which given below

foo1

 +----+---------+-----------------+----------------+-------------------+---------------------+
    | Id | OrderId |    OrderNote    | SentSalesmanId | OrderedSalesmanId | ConfirmedSalesmanId |
    +----+---------+-----------------+----------------+-------------------+---------------------+
    |  2 |    1    | important order |        1       |         2         |          3          |
    +----+---------+-----------------+----------------+-------------------+---------------------+

and salesman table which contains Id, Code and Name.

salesman

+----+-------+-------+
| Id | Code  | Name  |
+----+-------+-------+
| 1  | S1001 | N1001 |
+----+-------+-------+
| 2  | S1002 | N1002 |
+----+-------+-------+
| 3  | S1003 | N1003 |
+----+-------+-------+

As you can see in table foo1 there are 3 columns(SentSalesmanId, OrderedSalesmanId and ConfirmedSalesmanId) related with salesman table.

Here is my query

SELECT
  f.Id,
  f.OrderId,
  f.OrderNote,
  s1.Code AS SentSalesmanCode,
  s2.Code AS OrderedSalesmanCode,
  s3.Code AS ConfirmedSalesmanCode
FROM
    foo1 f
INNER JOIN salesman s1 ON (s1.Id = f.SentSalesmanId)
INNER JOIN salesman s2 ON (s2.Id = f.OrderedSalesmanId)
INNER JOIN salesman s3 ON (s3.Id = f.ConfirmedSalesmanId);

Here is my expected output

+----+---------+------------------+------------------+---------------------+-----------------------+
| Id | OrderId | OrderNote        | SentSalesmanCode | OrderedSalesmanCode | ConfirmedSalesmanCode |
+----+---------+------------------+------------------+---------------------+-----------------------+
|  2 |    1    | important order! |       S1001      |        S1002        |         S1003         |
+----+---------+------------------+------------------+---------------------+-----------------------+

This query gives what I want, but it works slow for big data. Do I have to use JOIN three times to salesman table? Can't I write it with one JOIN or Is there any way which is more efficient?

Thanks.

Try to run EXPLAIN SELECT .... it will tell you why it is slow. This is correct way of joining. How many records do you have? — martin.malek
– martin.malek, Commented Aug 10, 2017 at 2:14
@KemalGüler This is nothing.. In this join case there can be problem when the salesman has many records. Try EXPLAIN and than try the query you have but as subquery, the parent query will do only COUNT(*). It's possible that there is not problem in the query but on network. Try to have a look at percona toolkit, there are more profiling tools for this. — martin.malek
– martin.malek, Commented Aug 10, 2017 at 6:38

Kevin · Accepted Answer · 2017-08-14 11:47:43Z

2

There is nothing wrong with your query. This should be fast. If it is going slow, you probably need to make sure your tables are indexed correctly.

SHOW INDEX FROM foo1
SHOW INDEX FROM salesman

would be helpful if you could add them to your question. We would then have # rows and cardinality for the fields involved.

edited Aug 14, 2017 at 11:47

answered Aug 9, 2017 at 11:23

Kevin

7,34913 gold badges50 silver badges72 bronze badges

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

2 Comments

Kemal Güler Over a year ago

Thanks for answer :)

Rick James Over a year ago

I still don't see the indexes on your tables. Please provide SHOW CREATE TABLE.

Wilson Hauck · Accepted Answer · 2017-08-19 17:08:19Z

0

There is nothing wrong with the query? A query with NO WHERE, ORDER BY. Please show us the entire query. Each SHOW CREATE TABLE table_name and each SHOW INDEX FROM table_name when you have time and someone will have an idea or two for you.

answered Aug 19, 2017 at 17:08

Wilson Hauck

2,3831 gold badge14 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL - Multiple JOIN on Same Table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related