mysql - 重建 MySQL 查询以保持低于 MAX_JOIN_SIZE 行

Question

我有一个 SQL 查询失败(大部分时间)，因为连接的行太多。 MySQL 提供的错误是 The SELECT would examine more than MAX_JOIN_SIZE rows;检查您的 WHERE 并使用 SET SQL_BIG_SELECTS=1 或 SET MAX_JOIN_SIZE=# 如果 SELECT 没问题。我知道我可以通过设置提到的变量 SQL_BIG_SELECTS 和 MAX_JOIN_SIZE 来避免错误，但我觉得这不是正确的方法，并且只会在未来稍微插入这个问题，因为 future 连接计数可能会增加。

事实:我有一个事件计划工具，可以将用户(=工作人员)分配给某些任务。这些表是 users (userid,username) [ID and name], tasks (taskid,task,start,end) [ID, task name, start as timestamp, end作为时间戳] 和 userassignment (id,userid,taskid,deleted) [ID, user assigned to a task, the task, is the assignment still valid..

准确的表定义是这样的:

CREATE TABLE users (
 userid INT NOT NULL AUTO_INCREMENT,
 username VARCHAR(250),
 PRIMARY KEY (userid)
);

CREATE TABLE tasks (
 taskid INT NOT NULL AUTO_INCREMENT,
 task VARCHAR(250),
 start INT,
 end INT,
 PRIMARY KEY (taskid),
 INDEX USING BTREE (start),
 INDEX USING BTREE (end)
);

CREATE TABLE userassignment (
 id INT NOT NULL AUTO_INCREMENT,
 userid INT,
 taskid INT,
 deleted TINYINT,
 PRIMARY KEY (id),
 INDEX USING BTREE (userid),
 INDEX USING BTREE (userid),
 UNIQUE KEY `usertasks` (  `userid` ,  `taskid` )
);

我需要知道分配了哪些用户以及在事件的哪几天(第 1 天、第 2 天、第 3 天)分配了他们。

我的查询是这样的:

SELECT
    u.userid,
    u.username,
    COUNT(ua.id) AS count_all,
    dayone.c AS count_one,
    daytwo.c AS count_two,
    daythree.c AS count_three
FROM
    users AS u
INNER JOIN
    userassignment AS ua ON ua.userid = u.userid AND ua.deleted = 0
INNER JOIN
    tasks AS t ON ua.taskid = t.taskid

    LEFT JOIN (
        SELECT
            u.userid,
            COUNT(ua.id) AS c
        FROM
            users AS u
        INNER JOIN
            userassignment AS ua ON
            ua.userid = u.userid AND
            ua.deleted = 0
        INNER JOIN
            tasks AS t ON
            ua.taskid = t.taskid
        WHERE
            t.start > UNIX_TIMESTAMP("2014-08-01 00:00:00") AND
            t.start < UNIX_TIMESTAMP("2014-08-02 00:00:00")
        GROUP BY
            u.userid
    ) AS dayone ON dayone.userid = u.userid

    LEFT JOIN (
        SELECT
            u.userid,
            COUNT(ua.id) AS c
        FROM
            users AS u
        INNER JOIN
            userassignment AS ua ON
            ua.userid = u.userid AND
            ua.deleted = 0
        INNER JOIN
            tasks AS t ON
            ua.taskid = t.taskid
        WHERE
            t.start > UNIX_TIMESTAMP("2014-07-31 00:00:00") AND
            t.start < UNIX_TIMESTAMP("2014-08-01 00:00:00")
        GROUP BY
            u.userid
    ) AS daytwo ON daytwo.userid = u.userid

    LEFT JOIN (
        SELECT
            u.userid,
            COUNT(ua.id) AS c
        FROM
            users AS u
        INNER JOIN
            userassignment AS ua ON
            ua.userid = u.userid AND
            ua.deleted = 0
        INNER JOIN
            tasks AS t ON
            ua.taskid = t.taskid
        WHERE
            t.start > UNIX_TIMESTAMP("2014-08-02 00:00:00") AND
            t.start < UNIX_TIMESTAMP("2014-08-04 00:00:00")
        GROUP BY
            u.userid
    ) AS daythree ON daythree.userid = u.userid

WHERE
    t.start > UNIX_TIMESTAMP("2014-07-31 00:00:00") AND
    t.start < UNIX_TIMESTAMP("2014-08-04 00:00:00")
GROUP BY
    u.userid
ORDER BY
    username ASC

首先，我选择了在三天中的某一天有任务的所有用户(数据库中的用户比分配给任务的用户多六倍)，然后我离开加入了这三天中每一天的分配用户。

那么，有没有办法重建查询以连接更少的行？我只需要知道，哪一天分配给谁，而不是分配的数量。

我已经尝试过 UNION 几个查询，但没有成功。

SQL Fiddle

真实查询(不在 SQL Fiddle 中)的 EXPLAIN 是:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY t   range   PRIMARY,start   start   5   NULL    120 100.00  Using where; Using index; Using temporary; Using filesort
1   PRIMARY ua  ref usertasks,userid,taskid taskid  2   db1154575-helfer.t.id   2   100.00  Using where
1   PRIMARY u   eq_ref  userid  userid  2   db1154575-helfer.ua.userid  1   100.00   
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    152 100.00   
1   PRIMARY <derived3>  ALL NULL    NULL    NULL    NULL    94  100.00   
1   PRIMARY <derived4>  ALL NULL    NULL    NULL    NULL    147 100.00   
4   DERIVED t   range   PRIMARY,start   start   5   NULL    53  100.00  Using where; Using index; Using temporary; Using filesort
4   DERIVED ua  ref usertasks,userid,taskid taskid  2   db1154575-helfer.t.id   2   100.00  Using where
4   DERIVED u   eq_ref  userid  userid  2   db1154575-helfer.ua.userid  1   100.00  Using index
3   DERIVED t   range   PRIMARY,start   start   5   NULL    21  100.00  Using where; Using index; Using temporary; Using filesort
3   DERIVED ua  ref usertasks,userid,taskid taskid  2   db1154575-helfer.t.id   2   100.00  Using where
3   DERIVED u   eq_ref  userid  userid  2   db1154575-helfer.ua.userid  1   100.00  Using index
2   DERIVED t   range   PRIMARY,start   start   5   NULL    44  100.00  Using where; Using index; Using temporary; Using filesort
2   DERIVED ua  ref usertasks,userid,taskid taskid  2   db1154575-helfer.t.id   2   100.00  Using where
2   DERIVED u   eq_ref  userid  userid  2   db1154575-helfer.ua.userid  1   100.00  Using index

Answer 1

那么，这真的只是一种冗长的说法吗...

SELECT u.*
     , DATE(FROM_UNIXTIME(t.start)) dt
     , COUNT(t.taskid) total
  FROM users u
  LEFT 
  JOIN userassignment ut
    ON ut.userid = u.userid
   AND ut.deleted = 0
  LEFT
  JOIN tasks t 
    ON t.taskid = ut.taskid
 GROUP
    BY u.userid
     , DATE(FROM_UNIXTIME(t.start))

在上面的示例中，您可以将 COUNT(t.taskid) 更改为 COUNT(CASE WHEN x = 'y' THEN z END) 或 SUM(CASE...