· 7 years ago · Nov 17, 2018, 11:20 AM
1SELECT
2 `lead`.id,
3 `lead`.user_id,
4 `lead`.time,
5 `lead`.status,
6 `lead`.name,
7 `lead`.source,
8 `lead_data`.value AS `email`
9FROM
10 `lead`
11LEFT OUTER JOIN `lead_group_link` ON `lead`.id = `lead_group_link`.lead_id
12LEFT OUTER JOIN `lead_data` ON `lead`.id = `lead_data`.lead_id
13AND `lead_data`.lead_data_type_id = 1
14LEFT OUTER JOIN `lead_data` AS `lead_data_search` ON `lead`.id = `lead_data_search`.lead_id
15WHERE
16 `lead`. STATUS < 3
17AND `lead`.user_id = 227
18AND(
19 `lead`.name LIKE '%blaine%'
20 OR `lead`.source LIKE '%blaine%'
21 OR `lead_data_search`.value LIKE '%blaine%'
22)
23GROUP BY
24 `lead`.id
25ORDER BY
26 `lead`.id DESC
27LIMIT 0,
28 10
29
30------------
31
321 SIMPLE lead ref user_id user_id 4 const 321 Using where; Using temporary; Using filesort
331 SIMPLE lead_group_link ref lead_id lead_id 4 socialn_alpha.lead.id 1 Using index
341 SIMPLE lead_data ref lead_data_type_id lead_data_type_id 5 const 1055
351 SIMPLE lead_data_search ALL 2111 Using where
36
37------------
38
39CREATE TABLE IF NOT EXISTS `lead` (
40 `id` int(10) NOT NULL AUTO_INCREMENT,
41 `user_id` int(10) NOT NULL,
42 `time` int(12) NOT NULL,
43 `status` tinyint(1) NOT NULL,
44 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
45 `source` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
46 `app_source` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
47 `account_id` int(10) DEFAULT NULL,
48 `external_id` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
49 PRIMARY KEY (`id`),
50 KEY `user_id` (`user_id`)
51) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1614 ;
52
53--
54-- Constraints for dumped tables
55--
56
57--
58-- Constraints for table `lead`
59--
60ALTER TABLE `lead`
61 ADD CONSTRAINT `lead_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
62
63
64CREATE TABLE IF NOT EXISTS `lead_data` (
65 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
66 `lead_data_type_id` int(11) DEFAULT NULL,
67 `lead_data_subtype_id` int(11) DEFAULT NULL,
68 `value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
69 `lead_id` int(11) NOT NULL,
70 `id` int(11) NOT NULL AUTO_INCREMENT,
71 PRIMARY KEY (`id`),
72 KEY `lead_data_type_id` (`lead_data_type_id`),
73 KEY `lead_data_subtype_id` (`lead_data_subtype_id`)
74) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3223 ;
75
76--
77-- Constraints for dumped tables
78--
79
80--
81-- Constraints for table `lead_data`
82--
83ALTER TABLE `lead_data`
84 ADD CONSTRAINT `lead_data_ibfk_1` FOREIGN KEY (`lead_data_type_id`) REFERENCES `lead_data_type` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
85 ADD CONSTRAINT `lead_data_ibfk_2` FOREIGN KEY (`lead_data_subtype_id`) REFERENCES `lead_data_subtype` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
86
87
88--
89-- Table structure for table `lead_group_link`
90--
91
92CREATE TABLE IF NOT EXISTS `lead_group_link` (
93 `lead_id` int(11) NOT NULL,
94 `lead_group_id` int(11) NOT NULL,
95 KEY `lead_id` (`lead_id`),
96 KEY `lead_group_id` (`lead_group_id`)
97) ENGINE=InnoDB DEFAULT CHARSET=latin1;
98
99--
100-- Constraints for dumped tables
101--
102
103--
104-- Constraints for table `lead_group_link`
105--
106ALTER TABLE `lead_group_link`
107 ADD CONSTRAINT `lead_group_link_ibfk_2` FOREIGN KEY (`lead_group_id`) REFERENCES `lead_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
108 ADD CONSTRAINT `lead_group_link_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `lead` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;