Query And Join Multiple Column on MySql

I have two table with big data in our MySQL database.

tbl_phonebook :

 PB_ID    CUST_NAME     PHONE1     PHONE2      PHONE3     PHONE4
 1        Richard       11111      12222       13333      14444
 2        Maria         21111      22222       23333      24444
 3        Robert        31111      32222       33333      34444

tbl_calllog :

 LOG_ID   CALL_TIME     PHONENUMBER   
 1        2020-06-01    22222       
 2        2020-06-01    55555
 3        2020-06-01    13333

How to get result like this with best performance :

 LOG_ID   CALL_TIME     PHONENUMBER   CUST_NAME
 1        2020-06-01    22222         Maria
 2        2020-06-01    55555         -
 3        2020-06-01    13333         Richard

Thanks.

Solution

Please try this query:

select 
  c.LOG_ID, c.CALL_TIME, c.PHONENUMBER, p.CUST_NAME 
from 
  tbl_calllog c 
left join 
  tbl_phonebook p 
on 
  c.PHONENUMBER in (p.PHONE1, p.PHONE2, p.PHONE3, p.PHONE4);

I'm afaid current query is not the best performance, because mysql have to check conditions in all phone columns.

Setup a new table to map phone number to user like the following:

create table tbl_phonebook (
  `PB_ID` int(11) NOT NULL,
  `CUST_NAME` varchar(11) DEFAULT NULL,
  `PHONE` int(11) DEFAULT NULL,
  PRIMARY KEY (`PB_ID`),
  index idx_p1 (PHONE)
) ENGINE=InnoDB;
insert into tbl_phonebook2 (CUST_NAME, PHONE)
VALUES ('Richard', 11111),
       ('Richard', 12222),
       ('Richard', 13333),
       ('Richard', 14444),
       ('Maria', 21111),
       ('Maria', 22222),
       ('Maria', 23333),
       ('Maria', 24444),
       ('Robert', 31111),
       ('Robert', 32222),
       ('Robert', 33333),
       ('Robert', 34444);

query will be:

select
       c.LOG_ID, c.CALL_TIME, c.PHONENUMBER, p.CUST_NAME
from
     tbl_calllog c
left join
    tbl_phonebook2 p
on c.PHONENUMBER = p.PHONE;

the result and execution plan are as following:

mysql> select
    ->        c.LOG_ID, c.CALL_TIME, c.PHONENUMBER, p.CUST_NAME
    -> from
    ->      tbl_calllog c
    -> left join
    ->     tbl_phonebook2 p
    -> on c.PHONENUMBER = p.PHONE;
+--------+------------+-------------+-----------+
| LOG_ID | CALL_TIME  | PHONENUMBER | CUST_NAME |
+--------+------------+-------------+-----------+
|      1 | 2020-06-01 |       22222 | Maria     |
|      2 | 2020-06-01 |       55555 | NULL      |
|      3 | 2020-06-01 |       13333 | Richard   |
+--------+------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> explain select
    ->        c.LOG_ID, c.CALL_TIME, c.PHONENUMBER, p.CUST_NAME
    -> from
    ->      tbl_calllog c
    -> left join
    ->     tbl_phonebook2 p
    -> on c.PHONENUMBER = p.PHONE;
+----+-------------+-------+------------+------+---------------+--------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL   | NULL    | NULL               |    3 |   100.00 | NULL  |
|  1 | SIMPLE      | p     | NULL       | ref  | idx_p1        | idx_p1 | 5       | test.c.PHONENUMBER |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)