· 6 years ago · Jul 04, 2019, 11:04 AM
1#Fetch 100 active info based on date_created, user and info_type.
2#Ordered by date_created and offset 100000
3SELECT i.id,
4 i.title,
5 i.view_count
6FROM info i
7WHERE i.date_created BETWEEN '2016-01-08 11:45:32' AND '2017-01-27 11:45:32'
8 AND i.info_type_id IN (1,2,3,23)
9 AND i.user_id IN (1,5,120,387,45023) #optional
10 AND i.id > 100000
11 AND i.date_deleted IS NULL
12ORDER BY i.date_created DESC
13LIMIT 100;
14
15
16#Fetch 100 active info based on date_created, info_type.
17#Ordered by view_count and offset 100000
18SELECT i.id,
19 i.title,
20 i.view_count
21FROM info i
22WHERE i.date_created BETWEEN '2016-01-08 11:45:32' AND '2017-01-27 11:45:32'
23 AND i.info_type_id IN (1,2,3,23)
24 AND i.user_id IN (1,5,120,387,45023) #optional
25 AND i.id > 100000
26 AND i.date_deleted IS NULL
27ORDER BY i.view_count DESC
28LIMIT 100;
29
30
31#Fetch 100 active info based on date_created, info_type, user and category.
32#Ordered by view_count and offset 100000
33SELECT i.id,
34 i.title,
35 i.view_count
36FROM info i
37 JOIN info_category ic on ic.info_id = i.id AND ic.category_id IN (1,2,3,4,10)
38WHERE i.date_created BETWEEN '2016-01-28 11:45:32' AND '2017-01-27 11:45:32'
39 AND i.info_type_id IN (1,2,3,23)
40 AND i.user_id IN (1,5,120,387,45023) #optional
41 AND i.id > 500000
42 AND i.date_deleted IS NULL
43GROUP BY i.id
44ORDER BY i.view_count DESC
45LIMIT 100;
46
47--
48-- Schema test
49--
50DROP SCHEMA IF EXISTS `test` ;
51CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
52SHOW WARNINGS;
53
54--
55-- Set default database
56--
57USE test;
58
59--
60-- Definition for table info_type
61--
62DROP TABLE IF EXISTS info_type;
63CREATE TABLE info_type (
64 id tinyint(1) UNSIGNED NOT NULL AUTO_INCREMENT,
65 name varchar(20) NOT NULL,
66 PRIMARY KEY (id)
67)
68ENGINE = INNODB
69CHARACTER SET utf8
70COLLATE utf8_general_ci;
71
72--
73-- Definition for table category
74--
75DROP TABLE IF EXISTS category;
76CREATE TABLE category (
77 id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
78 name varchar(20) NOT NULL,
79 PRIMARY KEY (id)
80)
81ENGINE = INNODB
82AUTO_INCREMENT = 1
83CHARACTER SET utf8
84COLLATE utf8_general_ci;
85
86--
87-- Definition for table user
88--
89DROP TABLE IF EXISTS user;
90CREATE TABLE user (
91 id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
92 name varchar(32) NOT NULL,
93 PRIMARY KEY (id)
94)
95ENGINE = INNODB
96AUTO_INCREMENT = 1
97CHARACTER SET utf8
98COLLATE utf8_general_ci;
99
100--
101-- Definition for table info
102--
103DROP TABLE IF EXISTS info;
104CREATE TABLE info (
105 id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
106 title varchar(255) NOT NULL,
107 date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
108 user_id int(10) UNSIGNED NOT NULL,
109 info_type_id tinyint(3) UNSIGNED NOT NULL,
110 date_deleted datetime DEFAULT NULL,
111 view_count int(11) UNSIGNED NOT NULL DEFAULT 0,
112 PRIMARY KEY (id),
113 INDEX date_created_idx (date_created),
114 INDEX fk_info_type_id_idx (info_type_id),
115 INDEX user_id_idx (user_id),
116 CONSTRAINT fk_info_type_id FOREIGN KEY (info_type_id)
117 REFERENCES info_type (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
118 CONSTRAINT fk_user_id FOREIGN KEY (user_id)
119 REFERENCES user (id) ON DELETE CASCADE ON UPDATE NO ACTION
120)
121ENGINE = INNODB
122AUTO_INCREMENT = 1
123CHARACTER SET utf8
124COLLATE utf8_general_ci;
125
126--
127-- Definition for table info_category
128--
129DROP TABLE IF EXISTS info_category;
130CREATE TABLE info_category (
131 info_id INT(10) UNSIGNED NOT NULL,
132 category_id INT(10) UNSIGNED NOT NULL,
133 PRIMARY KEY (info_id, category_id),
134 INDEX k_category_id_idx (category_id),
135 CONSTRAINT fk_info_id FOREIGN KEY (info_id)
136 REFERENCES info(id) ON DELETE CASCADE ON UPDATE NO ACTION,
137 CONSTRAINT fk_category_id FOREIGN KEY (category_id)
138 REFERENCES category(id) ON DELETE CASCADE ON UPDATE NO ACTION
139)
140ENGINE = INNODB
141CHARACTER SET utf8
142COLLATE utf8_general_ci;
143
144INSERT INTO info_type(name)
145 VALUES ('news'), ('announcement'), ('advice'), ('memo'), ('historic');
146
147DELIMITER //
148
149DROP PROCEDURE IF EXISTS createFakeData//
150CREATE PROCEDURE createFakeData ()
151BEGIN
152
153 DECLARE userCount int default 50000;
154 DECLARE infoCount int default 1000000;
155 DECLARE categoryCount int DEFAULT 1000;
156 DECLARE infoCategoryCount int;
157 DECLARE i int DEFAULT 1;
158 DECLARE j int;
159
160 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;
161
162 #create fake users
163 WHILE i < userCount DO
164 INSERT INTO user(name) VALUES (CONCAT('user_', i));
165 SET i := i + 1;
166 END WHILE;
167
168 SET i = 1;
169
170 #create fake infos
171 WHILE i < infoCount DO
172 INSERT INTO info(title,
173 date_created,
174 user_id,
175 info_type_id,
176 view_count)
177 VALUES (CONCAT('title_', i),
178 DATE_ADD(NOW(), INTERVAL -(FLOOR(RAND() * 365) + 1) DAY),
179 FLOOR(RAND() * userCount) + 1,
180 FLOOR(RAND() * 5) + 1,
181 FLOOR(RAND() * 10000) + 1);
182
183 SET i := i + 1;
184 END WHILE;
185
186 SET i = 1;
187
188 #create fake categories
189 WHILE i < categoryCount DO
190 INSERT INTO category(name) VALUES (CONCAT('category_', i));
191 SET i = i + 1;
192 END WHILE;
193
194 SET i = 1;
195
196 #create fake info-category associations
197 WHILE i < infoCount DO
198 SET infoCategoryCount = FLOOR(RAND() * 10) + 1;
199 SET j = 0;
200
201 WHILE j < infoCategoryCount DO
202 INSERT INTO info_category (info_id, category_id)
203 VALUES (i, FLOOR(RAND() * categoryCount) + 1);
204 SET j = j + 1;
205 END WHILE;
206
207 SET i = i + 1;
208 END WHILE;
209END//
210
211DELIMITER ;
212
213CALL createFakeData();
214DROP PROCEDURE createFakeData;
215
216INDEX(date_created)