· 8 years ago · May 24, 2018, 12:42 AM
1SELECT Parts.*, Image.type, Image.width, Image.height,
2(SELECT name FROM Location_State WHERE id = Parts.state_id) AS state,
3(SELECT name FROM Location_Region WHERE id = Parts.region_id) AS region,
4(SELECT start_date FROM Promotion WHERE id = Parts.promotion_id) AS promotion_start_date,
5(SELECT end_date FROM Promotion WHERE id = Parts.promotion_id) AS promotion_end_date
6FROM ( SELECT parts_id FROM Parts_Category WHERE Parts_Category.category_id = '40'
7UNION SELECT parts_id FROM Parts_Category WHERE Parts_Category.main_category_id = '40') cid
8LEFT JOIN Image ON Parts.image_id = Image.id
9JOIN Parts ON Parts.id = cid.parts_id AND Parts.status = 'A'
10ORDER BY Parts.level DESC, Parts.warehouse DESC, Parts.updated DESC LIMIT 0, 15
11
12(status, level, warehouse, updated)
13
14SELECT p.*, i.type, i.width, i.height,
15 (SELECT name FROM Location_State WHERE id = p.state_id) AS state,
16 (SELECT name FROM Location_Region WHERE id = p.region_id) AS region,
17 (SELECT start_date FROM Promotion WHERE id = p.promotion_id) AS promotion_start_date,
18 (SELECT end_date FROM Promotion WHERE id = p.promotion_id) AS promotion_end_date
19FROM parts p
20LEFT JOIN
21 image i
22ON i.id = p.image_id
23WHERE EXISTS (
24 SELECT NULL
25 FROM Parts_Category pc
26 WHERE pc.category_id = '40'
27 AND pc.parts_id = p.id
28 UNION ALL
29 SELECT NULL
30 FROM Parts_Category pc
31 WHERE pc.main_category_id = '40'
32 AND pc.parts_id = p.id
33 )
34 AND p.status = 'A'
35ORDER BY
36 p.status DESC, p.level DESC, p.warehouse DESC, p.updated DESC
37LIMIT 15
38
39parts (status, level, warehouse, updated) -- this one you have
40parts_category (category_id, parts_id)
41parts_category (main_category_id, parts_id)
42
43DROP TABLE IF EXISTS `test`.`image`;
44CREATE TABLE `test`.`image` (
45 `id` int(11) NOT NULL,
46 `type` int(11) NOT NULL,
47 `width` int(11) NOT NULL,
48 `height` int(11) NOT NULL,
49 PRIMARY KEY (`id`)
50) ENGINE=InnoDB DEFAULT CHARSET=utf8;
51
52DROP TABLE IF EXISTS `test`.`location_region`;
53CREATE TABLE `test`.`location_region` (
54 `id` int(11) NOT NULL,
55 `name` varchar(20) NOT NULL,
56 PRIMARY KEY (`id`)
57) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58
59DROP TABLE IF EXISTS `test`.`location_state`;
60CREATE TABLE `test`.`location_state` (
61 `id` int(11) NOT NULL,
62 `name` varchar(20) NOT NULL,
63 PRIMARY KEY (`id`)
64) ENGINE=InnoDB DEFAULT CHARSET=utf8;
65
66DROP TABLE IF EXISTS `test`.`parts`;
67CREATE TABLE `test`.`parts` (
68 `id` int(11) NOT NULL,
69 `status` char(1) NOT NULL,
70 `level` int(11) NOT NULL,
71 `warehouse` int(11) NOT NULL,
72 `updated` int(11) NOT NULL,
73 `state_id` int(11) NOT NULL,
74 `region_id` int(11) NOT NULL,
75 `promotion_id` int(11) NOT NULL,
76 `image_id` int(11) NOT NULL DEFAULT '1',
77 PRIMARY KEY (`id`),
78 KEY `status` (`status`,`level`,`warehouse`,`updated`)
79) ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81DROP TABLE IF EXISTS `test`.`parts_category`;
82CREATE TABLE `test`.`parts_category` (
83 `id` int(11) NOT NULL,
84 `parts_id` int(11) NOT NULL,
85 `category_id` int(11) NOT NULL,
86 `main_category_id` int(11) NOT NULL,
87 PRIMARY KEY (`id`),
88 KEY `ix_pc_cat_parts` (`category_id`,`parts_id`),
89 KEY `ix_pc_main_parts` (`main_category_id`,`parts_id`)
90) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91
92DROP TABLE IF EXISTS `test`.`promotion`;
93CREATE TABLE `test`.`promotion` (
94 `id` int(11) NOT NULL,
95 `start_date` datetime NOT NULL,
96 `end_date` datetime NOT NULL,
97 PRIMARY KEY (`id`)
98) ENGINE=InnoDB DEFAULT CHARSET=utf8;
99
100INSERT
101INTO parts
102SELECT id,
103 CASE WHEN RAND() < 0.1 THEN 'A' ELSE 'B' END,
104 RAND() * 100,
105 RAND() * 100,
106 RAND() * 100,
107 RAND() * 50,
108 RAND() * 50,
109 RAND() * 50,
110 RAND() * 50
111FROM t_source
112LIMIT 500000;
113INSERT
114INTO parts_category
115SELECT id,
116 id,
117 RAND() * 100,
118 RAND() * 100
119FROM t_source
120LIMIT 500000;
121INSERT
122INTO location_state
123SELECT id, CONCAT('State ', id)
124FROM t_source
125LIMIT 1000;
126INSERT
127INTO location_region
128SELECT id, CONCAT('Region ', id)
129FROM t_source
130LIMIT 1000;
131INSERT
132INTO promotion
133SELECT id,
134 '2009-07-22' - INTERVAL RAND() * 5 - 20 DAY,
135 '2009-07-22' - INTERVAL RAND() * 5 DAY
136FROM t_source
137LIMIT 1000;
138
1391, 'PRIMARY', 'p', 'ref', 'status', 'status', '3', 'const', 107408, 'Using where'
1401, 'PRIMARY', 'i', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.image_id', 1, ''
1416, 'DEPENDENT SUBQUERY', 'pc', 'ref', 'ix_pc_cat_parts', 'ix_pc_cat_parts', '8', 'const,test.p.id', 1, 'Using index'
1427, 'DEPENDENT UNION', 'pc', 'ref', 'ix_pc_main_parts', 'ix_pc_main_parts', '8', 'const,test.p.id', 1, 'Using index'
143, 'UNION RESULT', '<union6,7>', 'ALL', '', '', '', '', , ''
1445, 'DEPENDENT SUBQUERY', 'Promotion', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.promotion_id', 1, ''
1454, 'DEPENDENT SUBQUERY', 'Promotion', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.promotion_id', 1, ''
1463, 'DEPENDENT SUBQUERY', 'Location_Region', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.region_id', 1, ''
1472, 'DEPENDENT SUBQUERY', 'Location_State', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.p.state_id', 1, ''
148
149SELECT
150 Parts.*,
151 Image.type, Image.width, Image.height,
152 Location_State.name AS state,
153 Location_Region.name AS region,
154 Promotion.start_date AS promotion_start_date,
155 Promotion.end_date AS promotion_end_date
156FROM Parts
157LEFT JOIN Image ON Parts.image_id = Image.id
158LEFT JOIN Location_State ON Parts.state_id = Location_State.id
159LEFT JOIN Location_Region ON Parts.state_id = Location_Region.id
160LEFT JOIN Promotion ON Parts.promotion_id = Promotion.id
161INNER JOIN Parts_Category ON (Parts_Category.category_id = 40 OR Parts_Category.main_category_id = 40)
162WHERE Parts.status = 'A'
163GROUP BY Parts.id
164ORDER BY Parts.level DESC, Parts.warehouse DESC, Parts.updated DESC LIMIT 0, 15
165
166SHOW CREATE TABLE Parts;
167
168EXPLAIN <my query here>G