20eucy2N

1SELECT c.id, c.disabled, c.user_id, c.cpi, c.payout, c.title, (IF (COUNT(v.cid) > 0,'Yes','No')) AS finish, c.featured, COUNT(DISTINCT v.id) AS `views`, COUNT(DISTINCT cl.id) AS `clicks`, COUNT(DISTINCT co.id) AS `conversions`
2FROM campaigns AS c
3LEFT JOIN views AS v ON v.cid = c.id
4LEFT JOIN clicks AS cl ON cl.cid = c.id
5LEFT JOIN conversions AS co ON co.cid = c.id
6WHERE c.user_id = 4
7GROUP BY c.id
8
9/* 0 rows affected, 3 rows found. Duration for 1 query: 107.984 sec. */
10
11EXPLAIN output:
12
13INSERT INTO `UnknownTable` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'c', 'ALL', NULL, NULL, NULL, NULL, 3, 'Using where; Using temporary; Using filesort');
14INSERT INTO `UnknownTable` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'v', 'ALL', NULL, NULL, NULL, NULL, 549, '');
15INSERT INTO `UnknownTable` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'cl', 'ALL', NULL, NULL, NULL, NULL, 883, '');
16INSERT INTO `UnknownTable` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'co', 'ALL', NULL, NULL, NULL, NULL, 318, '');
17
18# --------------------------------------------------------
19# Host:                         127.0.0.1
20# Server version:               5.5.16-log
21# Server OS:                    Win32
22# HeidiSQL version:             6.0.0.3603
23# Date/time:                    2011-10-06 14:23:08
24# --------------------------------------------------------
25
26/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
27/*!40101 SET NAMES utf8 */;
28/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
29/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
30
31# Dumping structure for table campaigns
32CREATE TABLE IF NOT EXISTS `campaigns` (
33  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
34  `user_id` int(11) NOT NULL,
35  `tid` int(11) NOT NULL,
36  `opt` tinyint(1) NOT NULL,
37  `title` varchar(255) NOT NULL,
38  `uri` varchar(255) NOT NULL,
39  `cpi` float NOT NULL,
40  `payout` float NOT NULL,
41  `cat` int(11) NOT NULL,
42  `featured` tinyint(4) NOT NULL,
43  `domain` int(11) NOT NULL,
44  `created` datetime NOT NULL,
45  `disabled` tinyint(4) NOT NULL,
46  `completed` tinyint(1) NOT NULL,
47  PRIMARY KEY (`id`)
48) ENGINE=InnoDB DEFAULT CHARSET=utf8;
49
50# Data exporting was unselected.
51
52
53# Dumping structure for table clicks
54CREATE TABLE IF NOT EXISTS `clicks` (
55  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
56  `timestamp` datetime NOT NULL,
57  `cid` int(11) NOT NULL,
58  `vid` int(11) NOT NULL,
59  `status` tinyint(1) NOT NULL,
60  `token` varchar(512) NOT NULL,
61  PRIMARY KEY (`id`)
62) ENGINE=InnoDB DEFAULT CHARSET=utf8;
63
64# Data exporting was unselected.
65
66
67# Dumping structure for table conversions
68CREATE TABLE IF NOT EXISTS `conversions` (
69  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
70  `timestamp` datetime NOT NULL,
71  `vid` int(11) NOT NULL,
72  `status` tinyint(1) NOT NULL,
73  `token` varchar(512) NOT NULL,
74  `cid` int(11) NOT NULL,
75  PRIMARY KEY (`id`)
76) ENGINE=InnoDB DEFAULT CHARSET=utf8;
77
78# Data exporting was unselected.
79
80
81# Dumping structure for table views
82CREATE TABLE IF NOT EXISTS `views` (
83  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
84  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
85  `vid` int(11) NOT NULL,
86  `cid` int(11) NOT NULL,
87  `ip` varchar(72) NOT NULL,
88  `token` varchar(256) NOT NULL,
89  PRIMARY KEY (`id`)
90) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91
92# Data exporting was unselected.
93/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
94/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
95/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;