56,675
社区成员
发帖
与我相关
我的任务
分享
-- 主要log表 无连续性增长字段
CREATE TABLE `data_log` (
`uniqueid` varchar(20) NOT NULL,
`lead_id` int(9) unsigned NOT NULL,
`event_time` datetime DEFAULT NULL,
`length_in_sec` int(10) DEFAULT NULL,
`status` varchar(6) DEFAULT NULL,
`user` varchar(20) DEFAULT NULL,
`comments` varchar(36) DEFAULT 'AUTO',
PRIMARY KEY (`uniqueid`),
KEY `lead_id` (`lead_id`),
KEY `event_time` (`call_date`),
KEY `status` (`status`),
KEY `user` (`user`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `data_users` (
`user_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(20) NOT NULL,
`pass` varchar(20) NOT NULL,
`full_name` varchar(50) DEFAULT NULL,
`user_level` tinyint(2) DEFAULT '1',
`user_group` varchar(20) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user` (`user`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
CREATE TABLE `data_status` (
`status` varchar(20) NOT NULL,
`status_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
mysql> EXPLAIN
-> select a.lead_id,a.call_date,a.user,b.full_name,a.status,c.status_name from data_log a
-> left join data_users b on a.user=b.user
-> left join data_status c on a.status=c.status
-> where a.event_time BETWEEN '2014-02-12 10:00:01' and '2014-02-12 23:00:01'
-> and a.status='NA' order by a.event_time desc limit 10000,10050;