MySQL-mysql 表连接优化问题

Question

pre_login表的DDL可简化为：

CREATE TABLE pre_login (
user_id bigint(20) unsigned NOT NULL DEFAULT '0',
c_date date NOT NULL,
KEY c_date,
) ENGINE=MyISAM

SELECT
    count(A.user_id)
FROM
    (
        SELECT DISTINCT
            user_id
        FROM
            `pre_login`
        WHERE
            c_date = '2014-10-09'
    ) AS A,
    (
        SELECT DISTINCT
            user_id
        FROM
                 `pre_login`
        WHERE
            c_date = '2014-10-08'
    ) AS B
WHERE
    A.user_id = B.user_id

Answer 1

加索引，速度杠杠的，路过

Answer 2

这个其实可以换个思路嘛，你只是想查询8号和9号都有的user_id有多少个，那完全可以这样写嘛

SELECT count(user_id) num,user_id
FROM `pre_login`
WHERE c_date IN ('2014-10-08', '2014-10-09')
GROUP BY user_id
HAVING count(user_id) > 1;

这样与你想要的结果其实是一样的了吧,只不过，最后需要对执行的SQL结果进行计数下。

或者再加一层子查询：

SELECT count(user_id)
FROM (
SELECT count(user_id) num,user_id
FROM `pre_login`
WHERE c_date IN ('2014-10-08', '2014-10-09')
GROUP BY user_id
HAVING count(user_id) > 1
) a;

这样最后得到的就是统计数了
修改：
鉴于楼主所描述逻辑可以继续修改这个SQL如下：
查询数据列表：

SELECT count(1) num, A.user_id
FROM (
SELECT count(1), user_id, c_date
FROM `newtest`
WHERE c_date IN ('2014-10-08', '2014-10-09')
GROUP BY user_id, c_date
) A
GROUP BY A.user_id
HAVING count(1) > 1

或者再加层来计数

SELECT count(1) num
FROM (
SELECT count(1) num, A.user_id
FROM (
SELECT count(1), user_id, c_date
FROM `newtest`
WHERE c_date IN ('2014-10-08', '2014-10-09')
GROUP BY user_id, c_date
) A
GROUP BY A.user_id
HAVING count(1) > 1
) B;

不过这么一来，子查询就有点多了，具体效率没测过了

Answer 3

我贴下你的那个查询的：

<img src='/theme/images/line.jpg' loadsrc="/62321251/201410211041368646.jpg" alt="实际中的索引：
KEY NewIndex1 (c_date,xxx),
KEY user_id (user_id)
" />

这个问题我最后是采用临时表解决的。效率较快，但是感觉较为麻烦。

Answer 4

应该是这个吧. 未验证.

select count(distinct A.user_id)
from `pre_login` A, `pre_login` B
where A.c_date='2014-10-09' and B.c_date='2014-10-08' and A.user_id = B.user_id;