多连接查询与使用函数之间的 MySQL 性能

Question

我有一个名为的表:candidate_info 包含三列，分别是 id_user、id_type 和 value:

• id_type = 1 => 全名
• id_type = 2 => 电子邮件
• id_type = 3 => 邮寄地址
• id_type = 4 => 电话号码

存储用户信息:

我考虑如下两个查询，它将从行到列组合:

表格原件:

Id User | Id Type | Value
1       | 1       | John
1       | 2       | john@g.com
1       | 3       | Ho Chi Minh
1       | 4       | 123
2       | 1       | Ana
2       | 2       | ana@g.com
2       | 3       | New York
2       | 4       | 456

致新人:

Id User | Fullname | Email        | Mailing_Address | Phone Number
1       | John     | john@g.com   | Ho Chi Minh     | 123
2       | Ana      | ana@g.com    | New York        | 456

<强>1。第一次查询:

select
    c1.id_user,
    c1.value as fullname,
    c2.value as email,
    c3.value as mailing_address,
    c4.value as phone_number
from
    candidate_info c1
        left join
    candidate_info c2 ON c1.id_user = c2.id_user
        left join
    candidate_info c3 ON c1.id_user = c3.id_user
        left join
    candidate_info c4 ON c1.id_user = c4.id_user
where
    c1.id_type = 1
    and c2.id_type = 2
    and c3.id_type = 3
    and c4.id_type = 4;

<强>2。第二个查询

select 
    c.id_user,
    MAX(IF(c.id_type = 1, c.value, NULL)) as fullname,
    MAX(IF(c.id_type = 2, c.value, NULL)) as email,
    MAX(IF(c.id_type = 3, c.value, NULL)) as mailing_address,
    MAX(IF(c.id_type = 4, c.value, NULL)) as phone_number
from
    candidate_info c
where
    c.id_type in (1, 2, 3, 4)
group by c.id_user

哪个更好？

编辑:将 id_entry_field 更改为 id_type，将 join 更改为 left join 以使其有意义。

Answer 1

第二个查询更好，因为它不像第一个查询那样创建大的笛卡尔积。相反，它只迭代表的记录一次，发出包含每个组的聚合数据的行。