9zugewps

1-- Table structure for table `temp_app`
2--
3
4CREATE TABLE IF NOT EXISTS `temp_app` (
5  `id` int(11) NOT NULL AUTO_INCREMENT,
6  `vid` int(5) NOT NULL,
7  `num` varchar(64) NOT NULL,
8  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
9  PRIMARY KEY (`id`),
10  KEY `vid` (`vid`),
11  KEY `num` (`num`),
12) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=69509;
13
14-- Table structure for table `inv_flags`
15--
16
17CREATE TABLE IF NOT EXISTS `inv_flags` (
18  `num` varchar(64) NOT NULL,
19  `vid` int(11) NOT NULL,
20  `f_special` tinyint(1) NOT NULL, /*0 or 1*/
21  `f_inserted` tinyint(1) NOT NULL, /*0 or 1*/
22  `f_notinserted` tinyint(1) NOT NULL, /*0 or 1*/
23  `userID` int(11) NOT NULL,
24  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
25  KEY `num` (`num`),
26  KEY `userID` (`userID`),
27  KEY `vid` (`vid`),
28  KEY `timestamp` (`timestamp`)
29) ENGINE=InnoDB DEFAULT CHARSET=utf8;
30	
31SELECT date_format(ifs.`timestamp`,'%y/%m/%d') as `date`
32                ,count(DISTINCT ta.num) as inserted /*Unique nums*/
33                ,SUM(ifs.f_notinserted) as not_inserted
34                ,SUM(ifs.f_special)  as special
35                ,count(ta.num) as links /*All nums*/
36                from inventory_flags ifs
37                LEFT JOIN temp_app ta ON ta.num = ifs.num AND ta.vid = ifs.vid
38                WHERE ifs.userID = 3
39                GROUP BY date(ifs.`timestamp`) DESC LIMIT 30
40	
41id  select_type table   type    possible_keys   key key_len ref rows    Extra
421   SIMPLE  ifs ref userID  userID  4   const   12153   Using where
431   SIMPLE  ta  ref vid,num num 194 ifs.num 1
44	
45select count(*) from (select distinct...