mysql - sql如何使用这种语法更轻量？

Question

您好，我收到此消息:

[The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]

关于这个语法:

    SELECT CONCAT(v2.meta_value, ' ', v3.meta_value) AS name,
 a.usr, a.vagtDato, b.timeloen, c.provision, d.kursus, e.trappetur, f.sygedag
FROM $main_table a
LEFT JOIN (SELECT usr, count(vagt_type) as timeloen
FROM $main_table  WHERE vagt_type = 'timeloen'
AND vagtDato between DATE('$start') AND DATE('$end')
GROUP BY usr ) b on b.usr=a.usr

LEFT JOIN (SELECT usr, count(vagt_type) as provision
FROM $main_table  WHERE vagt_type = 'provision'
AND vagtDato between DATE('$start') AND DATE('$end')
GROUP BY usr ) c on c.usr=a.usr

LEFT JOIN (SELECT usr, count(vagt_type) as kursus
FROM $main_table  WHERE vagt_type = 'kursus'
AND vagtDato between DATE('$start') AND DATE('$end')
GROUP BY usr ) d on d.usr=a.usr

LEFT JOIN (SELECT usr, count(vagt_type) as trappetur
FROM $main_table  WHERE vagt_type = 'trappetur'
AND vagtDato between DATE('$start') AND DATE('$end')
GROUP BY usr ) e on e.usr=a.usr

LEFT JOIN (SELECT usr, count(vagt_type) as sygedag
FROM $main_table  WHERE vagt_type = 'sygedag'
AND vagtDato between DATE('$start') AND DATE('$end')
GROUP BY usr ) f on f.usr=a.usr

LEFT JOIN Lausten_usermeta v2 ON a.usr=v2.user_id and v2.meta_key = 'first_name'
LEFT JOIN Lausten_usermeta v3 ON a.usr=v3.user_id and v3.meta_key = 'last_name'

WHERE a.vagtDato between DATE('$start') AND DATE('$end')
GROUP BY a.usr
ORDER BY $orderby $order LIMIT %d OFFSET %d

所以我知道我可以设置更高的限制，但我想避免这种情况!

有没有办法让这个语法变得更轻量级？

Answer 1

您可以对子查询使用条件聚合:

LEFT JOIN (SELECT usr,
                  sum(vagt_type = 'timeloen') as timeloen,
                  sum(vagt_type = 'provision') as provision,
                  . . .
           FROM $main_table 
           WHERE vagtDato between DATE('$start') AND DATE('$end')
           GROUP BY usr
          ) b
          on b.usr = a.usr

这将用一个子查询替换所有子查询。