There are 2 table [color=#FF0000]lasttime_table[/color] (log [color=#FF00FF]regt

There are 2 table lasttime_table (log regtime_table (schedule) registered schedule)
Note: the two table number is the same, and the amount of the data is one hundred million data above
CREATE TABLE `lasttime_table` (
`uid` int(25) unsigned NOT NULL COMMENT 'The user ID',
`lasttime` int(11) NOT NULL DEFAULT '0' COMMENT 'Last login time',
UNIQUE KEY `uid` (`uid`),
KEY `lasttime` (`lasttime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `regtime_table` (
`uid` int(11) unsigned NOT NULL COMMENT 'The user ID',
`regtime` int(11) NOT NULL DEFAULT '0' COMMENT 'Registration time',
PRIMARY KEY (`uid`),
KEY `rtime` (`rtime`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Question 1: I think what is the active data users?
The number of active users login - day = registration number

I like 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;
I like MySQL very long calculation time, I want to know where I have not optimized! You have to master help, who can also optimize?

Started by Parker at February 09, 2016 - 7:37 PM

Post explain

Posted by Estelle at November 21, 2016 - 4:46 PM

The number of log - day registration number


WHERE m.lasttime>1373731200 AND p.regtime<1373731200;


You are obviously not the day should be.
WHERE m.lasttime between xxx and 1373731200 AND p.regtime between xxx and 1373731200;

Posted by Sidney at December 04, 2016 - 5:20 PM

The CREATE TABLE statement gave ah, this two meter is temporarily constructed´╝ü

Posted by Parker at December 15, 2016 - 5:54 PM

Put a explain so hard? Not only don't structure, and your data distribution

Posted by Estelle at December 20, 2016 - 6:07 PM

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

Posted by Parker at January 01, 2017 - 7:10 PM

Allows you to post implementation of 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> AND; 1373731200 p.regtime<1373731200; the results of

Posted by Estelle at January 03, 2017 - 6:08 PM

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

Posted by Parker at January 03, 2017 - 7:20 PM

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

Posted by Parker at January 10, 2017 - 6:52 PM