有2个表 lasttime_table(登录时间表) regtime_table(注册时间表)
注释:两个表的数据条数是一样的,且数据量都得到一亿条数据以上
CREATE TABLE `lasttime_table` (
`uid` int(25) unsigned NOT NULL COMMENT '用户id',
`lasttime` int(11) NOT NULL DEFAULT '0' COMMENT '最后一次登录时间',
UNIQUE KEY `uid` (`uid`),
KEY `lasttime` (`lasttime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `regtime_table` (
`uid` int(11) unsigned NOT NULL COMMENT '用户id',
`regtime` int(11) NOT NULL DEFAULT '0' COMMENT '注册时间',
PRIMARY KEY (`uid`),
KEY `rtime` (`rtime`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
问题1:我想得到用户的活跃数据是多少?
用户活跃=当天登录数量-当天注册数量我的mysql这样的
SELECT count(m.uid) AS count FROM lasttime_table AS m LEFT JOIN regtime_table AS p ON m.uid=p.uid WHERE m.lasttime>1373731200 AND p.regtime<1373731200;
我这样的mysql计算时间很长,我想知道我哪里还没有优化!各位高手纷纷来帮忙下,有那些还可以优化的?
注释:两个表的数据条数是一样的,且数据量都得到一亿条数据以上
CREATE TABLE `lasttime_table` (
`uid` int(25) unsigned NOT NULL COMMENT '用户id',
`lasttime` int(11) NOT NULL DEFAULT '0' COMMENT '最后一次登录时间',
UNIQUE KEY `uid` (`uid`),
KEY `lasttime` (`lasttime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE `regtime_table` (
`uid` int(11) unsigned NOT NULL COMMENT '用户id',
`regtime` int(11) NOT NULL DEFAULT '0' COMMENT '注册时间',
PRIMARY KEY (`uid`),
KEY `rtime` (`rtime`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
问题1:我想得到用户的活跃数据是多少?
用户活跃=当天登录数量-当天注册数量我的mysql这样的
SELECT count(m.uid) AS count FROM lasttime_table AS m LEFT JOIN regtime_table AS p ON m.uid=p.uid WHERE m.lasttime>1373731200 AND p.regtime<1373731200;
我这样的mysql计算时间很长,我想知道我哪里还没有优化!各位高手纷纷来帮忙下,有那些还可以优化的?
WHERE m.lasttime>1373731200 AND p.regtime<1373731200;
你这明显不是当天的啊 当天的应该
WHERE m.lasttime between xxx and 1373731200 AND p.regtime between xxx and 1373731200;
贴个explain这么难吗?不光别结构,还有你的数据分布
explain lasttime_table
Field Type Null Key Default Extra
uid int(25) unsigned NO PRI NULL
lasttime int(11) NO MUL 0
explain regtime_table
Field Type Null Key Default Extra
uid int(11) unsigned NO PRI NULL
rtime int(11) NO MUL 0
explain lasttime_table
Field Type Null Key Default Extra
uid int(25) unsigned NO PRI NULL
lasttime int(11) NO MUL 0
explain regtime_table
Field Type Null Key Default Extra
uid int(11) unsigned NO PRI NULL
rtime int(11) NO MUL 0
是让你贴出执行explain SELECT count(m.uid) AS count FROM lasttime_table AS m LEFT JOIN regtime_table AS p ON m.uid=p.uid WHERE m.lasttime>1373731200 AND p.regtime<1373731200;的结果
1 SIMPLE m ALL uid,lasttime NULL NULL NULL 773512 Using where
1 SIMPLE p eq_ref PRIMARY,rtime PRIMARY 4 lasttime_table.m.uid 1 Using where
explain lasttime_table
Field Type Null Key Default Extra
uid int(25) unsigned NO PRI NULL
lasttime int(11) NO MUL 0
explain regtime_table
Field Type Null Key Default Extra
uid int(11) unsigned NO PRI NULL
rtime int(11) NO MUL 0
是让你贴出执行explain SELECT count(m.uid) AS count FROM lasttime_table AS m LEFT JOIN regtime_table AS p ON m.uid=p.uid WHERE m.lasttime>1373731200 AND p.regtime<1373731200;的结果
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE m ALL uid,lasttime NULL NULL NULL 773512 Using where
1 SIMPLE p eq_ref PRIMARY,rtime PRIMARY 4 lasttime_table.m.uid 1 Using where