1 | == Test setup |
---|---|
2 | ``` |
3 | populateWithTestData.php --users 50 --lists 20 --entries 2000 |
4 | UPDATE reading_list SET rl_date_updated = DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2017-10-01 14:53:27') + FLOOR(0 + (RAND() * 630720))), '%Y%m%d%H%i%s'); |
5 | UPDATE reading_list_entry SET rle_date_updated = DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2017-10-01 14:53:27') + FLOOR(0 + (RAND() * 630720))), '%Y%m%d%H%i%s'); |
6 | ``` |
7 | |
8 | == Test queries |
9 | |
10 | ``` |
11 | EXPLAIN SELECT /* ReadingListRepository::getListCount */ |
12 | COUNT(*) AS `rowcount` FROM ( |
13 | SELECT 1 FROM `reading_list` |
14 | WHERE rl_user_id = '1000' |
15 | AND rl_deleted = '0' |
16 | ) `tmp_count`; |
17 | EXPLAIN SELECT /* ReadingListRepository::getEntryCount */ |
18 | COUNT(*) AS `rowcount` FROM ( |
19 | SELECT 1 FROM `reading_list_entry` |
20 | WHERE rle_rl_id = '2' |
21 | AND rle_deleted = '0' |
22 | ) `tmp_count`; |
23 | |
24 | EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name) */ |
25 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
26 | FROM `reading_list` |
27 | WHERE rl_user_id = '1000' |
28 | AND rl_deleted = '0' |
29 | ORDER BY rl_name asc,rl_id asc |
30 | LIMIT 1000; |
31 | EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name, paging) */ |
32 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
33 | FROM `reading_list` |
34 | WHERE rl_user_id = '1000' |
35 | AND rl_deleted = '0' |
36 | AND ((rl_name > 'default') OR ((rl_name = 'default') AND (rl_id >= 1000))) |
37 | ORDER BY rl_name asc,rl_id asc |
38 | LIMIT 1; |
39 | EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated) */ |
40 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
41 | FROM `reading_list` |
42 | WHERE rl_user_id = '1000' |
43 | AND rl_deleted = '0' |
44 | ORDER BY rl_date_updated asc,rl_id asc |
45 | LIMIT 1000; |
46 | EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated, paging) */ |
47 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
48 | FROM `reading_list` |
49 | WHERE rl_user_id = '1000' |
50 | AND rl_deleted = '0' |
51 | AND ((rl_date_updated > '20170101000000') OR ((rl_date_updated = '20170101000000') AND (rl_id >= 1000))) |
52 | ORDER BY rl_date_updated asc,rl_id asc |
53 | LIMIT 1; |
54 | |
55 | EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title) */ |
56 | rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
57 | FROM `reading_list_entry`,`reading_list_project` |
58 | WHERE (rle_rlp_id = rlp_id) |
59 | AND rle_rl_id IN ('10') |
60 | AND rle_user_id = '1000' |
61 | AND rle_deleted = '0' |
62 | ORDER BY rle_title asc,rle_id asc |
63 | LIMIT 1000; |
64 | EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title, paging) */ |
65 | rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
66 | FROM `reading_list_entry`,`reading_list_project` |
67 | WHERE (rle_rlp_id = rlp_id) |
68 | AND rle_rl_id IN ('10') |
69 | AND rle_user_id = '1000' |
70 | AND rle_deleted = '0' |
71 | AND ((rle_title > 'Test_50') OR ((rle_title = 'Test_50') AND (rle_id >= 1000))) |
72 | ORDER BY rle_title asc,rle_id asc |
73 | LIMIT 2; |
74 | EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by updated) */ |
75 | rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
76 | FROM `reading_list_entry`,`reading_list_project` |
77 | WHERE (rle_rlp_id = rlp_id) |
78 | AND rle_rl_id IN ('10') |
79 | AND rle_user_id = '1000' AND |
80 | rle_deleted = '0' |
81 | ORDER BY rle_date_updated asc,rle_id asc |
82 | LIMIT 1000; |
83 | EXPLAIN SELECT /* ReadingListRepository::getListEntries (multiple lists) */ |
84 | rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
85 | FROM `reading_list_entry`,`reading_list_project` |
86 | WHERE (rle_rlp_id = rlp_id) |
87 | AND rle_rl_id IN ('10') |
88 | AND rle_user_id = '1000' |
89 | AND rle_deleted = '0' |
90 | ORDER BY rle_title asc,rle_id asc |
91 | LIMIT 1000; |
92 | |
93 | EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */ |
94 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
95 | FROM `reading_list` |
96 | WHERE rl_user_id = '1' |
97 | AND rl_deleted IN ('0','1') |
98 | AND (rl_date_updated > '20100101000000') |
99 | ORDER BY rl_name asc,rl_id asc |
100 | LIMIT 1000; |
101 | EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */ |
102 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
103 | FROM `reading_list` |
104 | WHERE rl_user_id = '1' |
105 | AND rl_deleted IN ('0','1') |
106 | AND (rl_date_updated > '20100101000000') |
107 | AND ((rl_name > 'Test_10') OR ((rl_name = 'Test_10') AND (rl_id >= 1000))) |
108 | ORDER BY rl_name asc,rl_id asc |
109 | LIMIT 1000; |
110 | EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */ |
111 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
112 | FROM `reading_list` |
113 | WHERE rl_user_id = '1000' |
114 | AND rl_deleted IN ('0','1') |
115 | AND (rl_date_updated > '20100101000000') |
116 | ORDER BY rl_date_updated asc,rl_id asc |
117 | LIMIT 1000; |
118 | EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */ |
119 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
120 | FROM `reading_list` |
121 | WHERE rl_user_id = '1000' |
122 | AND rl_deleted IN ('0','1') |
123 | AND (rl_date_updated > '20100101000000') |
124 | AND ((rl_date_updated > '20150101000000') OR ((rl_date_updated = '20150101000000') AND (rl_id >= 1000))) |
125 | ORDER BY rl_date_updated asc,rl_id asc |
126 | LIMIT 10; |
127 | |
128 | EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated */ |
129 | rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
130 | FROM `reading_list`,`reading_list_entry`,`reading_list_project` |
131 | WHERE (rl_id = rle_rl_id) |
132 | AND (rle_rlp_id = rlp_id) |
133 | AND rle_user_id = '1000' |
134 | AND rl_deleted = '0' |
135 | AND (rle_date_updated > '20100101000000') |
136 | ORDER BY rle_date_updated asc,rle_id asc |
137 | LIMIT 1000; |
138 | EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated (paging) */ |
139 | rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
140 | FROM `reading_list`,`reading_list_entry`,`reading_list_project` |
141 | WHERE (rl_id = rle_rl_id) |
142 | AND (rle_rlp_id = rlp_id) |
143 | AND rle_user_id = '1000' |
144 | AND rl_deleted = '0' |
145 | AND (rle_date_updated > '20100101000000') |
146 | AND ((rle_date_updated > '20150101000000') OR ((rle_date_updated = '20150101000000') AND (rle_id >= 1000))) |
147 | ORDER BY rle_date_updated asc,rle_id asc |
148 | LIMIT 10; |
149 | |
150 | EXPLAIN SELECT /* ReadingListRepository::getListsByPage */ |
151 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
152 | FROM `reading_list`,`reading_list_entry` |
153 | WHERE (rl_id = rle_rl_id) |
154 | AND rle_user_id = '1000' |
155 | AND rle_rlp_id = '1' |
156 | AND rle_title = 'Test_1' |
157 | AND rl_deleted = '0' |
158 | AND rle_deleted = '0' |
159 | GROUP BY rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
160 | ORDER BY rle_rl_id ASC |
161 | LIMIT 1; |
162 | |
163 | EXPLAIN SELECT /* ReadingListRepository::getListsByPage (paging) */ |
164 | rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
165 | FROM `reading_list`,`reading_list_entry` |
166 | WHERE (rl_id = rle_rl_id) |
167 | AND rle_user_id = '1000' |
168 | AND rle_rlp_id = '1' |
169 | AND rle_title = 'Test_1' |
170 | AND rl_deleted = '0' |
171 | AND rle_deleted = '0' |
172 | AND (rle_rl_id >= 1000) |
173 | GROUP BY rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
174 | ORDER BY rle_rl_id ASC |
175 | LIMIT 1; |
176 | EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */ |
177 | rl_id |
178 | FROM `reading_list` |
179 | WHERE rl_deleted = '1' |
180 | AND (rl_date_updated < '20100101000000') |
181 | LIMIT 1000; |
182 | |
183 | EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */ |
184 | rle_id |
185 | FROM `reading_list_entry` |
186 | WHERE rle_deleted = '1' |
187 | AND (rle_date_updated < '20100101000000') |
188 | LIMIT 1000; |
189 | ``` |
190 | |
191 | == Indexes |
192 | ``` |
193 | (20:17) root@localhost:[wiki]> SHOW INDEX FROM reading_list; |
194 | +--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
195 | | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
196 | +--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
197 | | reading_list | 0 | PRIMARY | 1 | rl_id | A | 1081 | NULL | NULL | | BTREE | | | |
198 | | reading_list | 0 | rl_user_deleted_updated_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | | |
199 | | reading_list | 0 | rl_user_deleted_updated_id | 2 | rl_deleted | A | 108 | NULL | NULL | | BTREE | | | |
200 | | reading_list | 0 | rl_user_deleted_updated_id | 3 | rl_date_updated | A | 1081 | NULL | NULL | | BTREE | | | |
201 | | reading_list | 0 | rl_user_deleted_updated_id | 4 | rl_id | A | 1081 | NULL | NULL | | BTREE | | | |
202 | | reading_list | 0 | rl_user_deleted_name_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | | |
203 | | reading_list | 0 | rl_user_deleted_name_id | 2 | rl_deleted | A | 108 | NULL | NULL | | BTREE | | | |
204 | | reading_list | 0 | rl_user_deleted_name_id | 3 | rl_name | A | 1081 | NULL | NULL | | BTREE | | | |
205 | | reading_list | 0 | rl_user_deleted_name_id | 4 | rl_id | A | 1081 | NULL | NULL | | BTREE | | | |
206 | | reading_list | 0 | rl_user_name_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | | |
207 | | reading_list | 0 | rl_user_name_id | 2 | rl_name | A | 1081 | NULL | NULL | | BTREE | | | |
208 | | reading_list | 0 | rl_user_name_id | 3 | rl_id | A | 1081 | NULL | NULL | | BTREE | | | |
209 | | reading_list | 0 | rl_user_updated_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | | |
210 | | reading_list | 0 | rl_user_updated_id | 2 | rl_deleted | A | 108 | NULL | NULL | | BTREE | | | |
211 | | reading_list | 0 | rl_user_updated_id | 3 | rl_date_updated | A | 1081 | NULL | NULL | | BTREE | | | |
212 | | reading_list | 0 | rl_user_updated_id | 4 | rl_id | A | 1081 | NULL | NULL | | BTREE | | | |
213 | | reading_list | 1 | rl_user_default | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | | |
214 | | reading_list | 1 | rl_user_default | 2 | rl_is_default | A | 216 | NULL | NULL | | BTREE | | | |
215 | | reading_list | 1 | rl_deleted_updated | 1 | rl_deleted | A | 2 | NULL | NULL | | BTREE | | | |
216 | | reading_list | 1 | rl_deleted_updated | 2 | rl_date_updated | A | 1081 | NULL | NULL | | BTREE | | | |
217 | +--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
218 | 20 rows in set (0.00 sec) |
219 | |
220 | (20:19) root@localhost:[wiki]> SHOW INDEX FROM reading_list_entry; |
221 | +--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
222 | | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
223 | +--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
224 | | reading_list_entry | 0 | PRIMARY | 1 | rle_id | A | 199500 | NULL | NULL | | BTREE | | | |
225 | | reading_list_entry | 0 | rle_list_deleted_title_id | 1 | rle_rl_id | A | 1995 | NULL | NULL | | BTREE | | | |
226 | | reading_list_entry | 0 | rle_list_deleted_title_id | 2 | rle_deleted | A | 1995 | NULL | NULL | | BTREE | | | |
227 | | reading_list_entry | 0 | rle_list_deleted_title_id | 3 | rle_title | A | 199500 | NULL | NULL | | BTREE | | | |
228 | | reading_list_entry | 0 | rle_list_deleted_title_id | 4 | rle_id | A | 199500 | NULL | NULL | | BTREE | | | |
229 | | reading_list_entry | 0 | rle_list_deleted_updated_id | 1 | rle_rl_id | A | 2015 | NULL | NULL | | BTREE | | | |
230 | | reading_list_entry | 0 | rle_list_deleted_updated_id | 2 | rle_deleted | A | 2078 | NULL | NULL | | BTREE | | | |
231 | | reading_list_entry | 0 | rle_list_deleted_updated_id | 3 | rle_date_updated | A | 199500 | NULL | NULL | | BTREE | | | |
232 | | reading_list_entry | 0 | rle_list_deleted_updated_id | 4 | rle_id | A | 199500 | NULL | NULL | | BTREE | | | |
233 | | reading_list_entry | 0 | rle_user_updated_id | 1 | rle_user_id | A | 102 | NULL | NULL | | BTREE | | | |
234 | | reading_list_entry | 0 | rle_user_updated_id | 2 | rle_date_updated | A | 199500 | NULL | NULL | | BTREE | | | |
235 | | reading_list_entry | 0 | rle_user_updated_id | 3 | rle_id | A | 199500 | NULL | NULL | | BTREE | | | |
236 | | reading_list_entry | 0 | rle_user_project_title | 1 | rle_user_id | A | 102 | NULL | NULL | | BTREE | | | |
237 | | reading_list_entry | 0 | rle_user_project_title | 2 | rle_rlp_id | A | 18136 | NULL | NULL | | BTREE | | | |
238 | | reading_list_entry | 0 | rle_user_project_title | 3 | rle_title | A | 199500 | NULL | NULL | | BTREE | | | |
239 | | reading_list_entry | 0 | rle_user_project_title | 4 | rle_rl_id | A | 199500 | NULL | NULL | | BTREE | | | |
240 | | reading_list_entry | 1 | rle_deleted_updated | 1 | rle_deleted | A | 2 | NULL | NULL | | BTREE | | | |
241 | | reading_list_entry | 1 | rle_deleted_updated | 2 | rle_date_updated | A | 199500 | NULL | NULL | | BTREE | | | |
242 | +--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
243 | 18 rows in set (0.00 sec) |
244 | |
245 | (20:19) root@localhost:[wiki]> SHOW INDEX FROM reading_list_project; |
246 | +----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
247 | | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
248 | +----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
249 | | reading_list_project | 0 | PRIMARY | 1 | rlp_id | A | 176 | NULL | NULL | | BTREE | | | |
250 | | reading_list_project | 0 | rlp_project | 1 | rlp_project | A | 176 | NULL | NULL | | BTREE | | | |
251 | +----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
252 | 2 rows in set (0.01 sec) |
253 | ``` |
254 | |
255 | == Plans |
256 | ``` |
257 | (20:16) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListCount */ |
258 | -> COUNT(*) AS `rowcount` FROM ( |
259 | -> SELECT 1 FROM `reading_list` |
260 | -> WHERE rl_user_id = '1000' |
261 | -> AND rl_deleted = '0' |
262 | -> ) `tmp_count`; |
263 | se+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
264 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
265 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
266 | | 1 | SIMPLE | reading_list | ref | rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_deleted_updated_id | 5 | const,const | 49 | Using index | |
267 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
268 | 1 row in set (0.01 sec) |
269 | |
270 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getEntryCount */ |
271 | -> COUNT(*) AS `rowcount` FROM ( |
272 | -> SELECT 1 FROM `reading_list_entry` |
273 | l -> WHERE rle_rl_id = '2' |
274 | -> AND rle_deleted = '0' |
275 | -> ) `tmp_count`; |
276 | +------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+ |
277 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
278 | +------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+ |
279 | | 1 | SIMPLE | reading_list_entry | ref | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_deleted_updated | rle_list_deleted_title_id | 5 | const,const | 1 | Using index | |
280 | +------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+ |
281 | 1 row in set (0.00 sec) |
282 | |
283 | (20:17) root@localhost:[wiki]> |
284 | L(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name) */ |
285 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
286 | _ -> FROM `reading_list` |
287 | -> WHERE rl_user_id = '1000' |
288 | |
289 | -> AND rl_deleted = '0' |
290 | -> ORDER BY rl_name asc,rl_id asc |
291 | ' -> LIMIT 1000; |
292 | r+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+ |
293 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
294 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+ |
295 | | 1 | SIMPLE | reading_list | ref | rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_deleted_name_id | 5 | const,const | 49 | Using where | |
296 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+ |
297 | 1 row in set (0.00 sec) |
298 | |
299 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name, paging) */ |
300 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
301 | d -> FROM `reading_list` |
302 | l -> WHERE rl_user_id = '1000' |
303 | e -> AND rl_deleted = '0' |
304 | -> AND ((rl_name > 'default') OR ((rl_name = 'default') AND (rl_id >= 1000))) |
305 | 0 -> ORDER BY rl_name asc,rl_id asc |
306 | '1 -> LIMIT 1; |
307 | ND+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+ |
308 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
309 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+ |
310 | | 1 | SIMPLE | reading_list | ref | PRIMARY,rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_deleted_name_id | 5 | const,const | 49 | Using where | |
311 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+ |
312 | 1 row in set (0.00 sec) |
313 | |
314 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated) */ |
315 | 0 -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
316 | -> FROM `reading_list` |
317 | -> WHERE rl_user_id = '1000' |
318 | -> AND rl_deleted = '0' |
319 | -> ORDER BY rl_date_updated asc,rl_id asc |
320 | -> LIMIT 1000; |
321 | d+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
322 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
323 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
324 | | 1 | SIMPLE | reading_list | ref | rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_deleted_updated_id | 5 | const,const | 49 | Using where | |
325 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
326 | 1 row in set (0.01 sec) |
327 | |
328 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated, paging) */ |
329 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
330 | _ -> FROM `reading_list` |
331 | r -> WHERE rl_user_id = '1000' |
332 | l -> AND rl_deleted = '0' |
333 | -> AND ((rl_date_updated > '20170101000000') OR ((rl_date_updated = '20170101000000') AND (rl_id >= 1000))) |
334 | -> ORDER BY rl_date_updated asc,rl_id asc |
335 | -> LIMIT 1; |
336 | e+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
337 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
338 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
339 | | 1 | SIMPLE | reading_list | ref | PRIMARY,rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_deleted_updated_id | 5 | const,const | 49 | Using where | |
340 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+ |
341 | 1 row in set (0.00 sec) |
342 | |
343 | (20:17) root@localhost:[wiki]> |
344 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title) */ |
345 | -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
346 | r -> FROM `reading_list_entry`,`reading_list_project` |
347 | e -> WHERE (rle_rlp_id = rlp_id) |
348 | d -> AND rle_rl_id IN ('10') |
349 | t -> AND rle_user_id = '1000' |
350 | -> AND rle_deleted = '0' |
351 | -> ORDER BY rle_title asc,rle_id asc |
352 | e -> LIMIT 1000; |
353 | 0+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
354 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
355 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
356 | | 1 | SIMPLE | reading_list_entry | ref | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title,rle_deleted_updated | rle_list_deleted_title_id | 5 | const,const | 1 | Using where | |
357 | | 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | | |
358 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
359 | 2 rows in set (0.00 sec) |
360 | |
361 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title, paging) */ |
362 | -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
363 | -> FROM `reading_list_entry`,`reading_list_project` |
364 | r -> WHERE (rle_rlp_id = rlp_id) |
365 | |
366 | -> AND rle_rl_id IN ('10') |
367 | g -> AND rle_user_id = '1000' |
368 | = -> AND rle_deleted = '0' |
369 | s -> AND ((rle_title > 'Test_50') OR ((rle_title = 'Test_50') AND (rle_id >= 1000))) |
370 | e -> ORDER BY rle_title asc,rle_id asc |
371 | -> LIMIT 2; |
372 | |
373 | GROUP BY+------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
374 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
375 | +------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
376 | | 1 | SIMPLE | reading_list_entry | ref | PRIMARY,rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title,rle_deleted_updated | rle_list_deleted_title_id | 5 | const,const | 1 | Using where | |
377 | | 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | | |
378 | +------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
379 | 2 rows in set (0.00 sec) |
380 | |
381 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by updated) */ |
382 | c -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
383 | -> FROM `reading_list_entry`,`reading_list_project` |
384 | -> WHERE (rle_rlp_id = rlp_id) |
385 | -> AND rle_rl_id IN ('10') |
386 | i -> AND rle_user_id = '1000' AND |
387 | , -> rle_deleted = '0' |
388 | i -> ORDER BY rle_date_updated asc,rle_id asc |
389 | -> LIMIT 1000; |
390 | = rle_rl_id) |
391 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+ |
392 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
393 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+ |
394 | | 1 | SIMPLE | reading_list_entry | ref | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title,rle_deleted_updated | rle_list_deleted_updated_id | 5 | const,const | 1 | Using where | |
395 | | 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | | |
396 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+ |
397 | 2 rows in set (0.00 sec) |
398 | |
399 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (multiple lists) */ |
400 | -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
401 | 0 -> FROM `reading_list_entry`,`reading_list_project` |
402 | -> WHERE (rle_rlp_id = rlp_id) |
403 | a -> AND rle_rl_id IN ('10') |
404 | de -> AND rle_user_id = '1000' |
405 | C -> AND rle_deleted = '0' |
406 | / -> ORDER BY rle_title asc,rle_id asc |
407 | -> LIMIT 1000; |
408 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
409 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
410 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
411 | | 1 | SIMPLE | reading_list_entry | ref | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title,rle_deleted_updated | rle_list_deleted_title_id | 5 | const,const | 1 | Using where | |
412 | | 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | | |
413 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+ |
414 | 2 rows in set (0.00 sec) |
415 | |
416 | (20:17) root@localhost:[wiki]> |
417 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */ |
418 | 0 -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
419 | g -> FROM `reading_list` |
420 | d -> WHERE rl_user_id = '1' |
421 | e_ -> AND rl_deleted IN ('0','1') |
422 | -> AND (rl_date_updated > '20100101000000') |
423 | -> ORDER BY rl_name asc,rl_id asc |
424 | -> LIMIT 1000; |
425 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+ |
426 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
427 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+ |
428 | | 1 | SIMPLE | reading_list | ref | rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_name_id | 4 | const | 3 | Using where | |
429 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+ |
430 | 1 row in set (0.00 sec) |
431 | |
432 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */ |
433 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
434 | -> FROM `reading_list` |
435 | -> WHERE rl_user_id = '1' |
436 | -> AND rl_deleted IN ('0','1') |
437 | -> AND (rl_date_updated > '20100101000000') |
438 | -> AND ((rl_name > 'Test_10') OR ((rl_name = 'Test_10') AND (rl_id >= 1000))) |
439 | -> ORDER BY rl_name asc,rl_id asc |
440 | -> LIMIT 1000; |
441 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+ |
442 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
443 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+ |
444 | | 1 | SIMPLE | reading_list | ref | PRIMARY,rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_name_id | 4 | const | 3 | Using where | |
445 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+ |
446 | 1 row in set (0.00 sec) |
447 | |
448 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */ |
449 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
450 | -> FROM `reading_list` |
451 | -> WHERE rl_user_id = '1000' |
452 | -> AND rl_deleted IN ('0','1') |
453 | -> AND (rl_date_updated > '20100101000000') |
454 | -> ORDER BY rl_date_updated asc,rl_id asc |
455 | -> LIMIT 1000; |
456 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+ |
457 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
458 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+ |
459 | | 1 | SIMPLE | reading_list | ref | rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_name_id | 4 | const | 49 | Using where; Using filesort | |
460 | +------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+ |
461 | 1 row in set (0.00 sec) |
462 | |
463 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */ |
464 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
465 | -> FROM `reading_list` |
466 | -> WHERE rl_user_id = '1000' |
467 | -> AND rl_deleted IN ('0','1') |
468 | -> AND (rl_date_updated > '20100101000000') |
469 | -> AND ((rl_date_updated > '20150101000000') OR ((rl_date_updated = '20150101000000') AND (rl_id >= 1000))) |
470 | -> ORDER BY rl_date_updated asc,rl_id asc |
471 | -> LIMIT 10; |
472 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+ |
473 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
474 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+ |
475 | | 1 | SIMPLE | reading_list | ref | PRIMARY,rl_user_deleted_updated_id,rl_user_deleted_name_id,rl_user_name_id,rl_user_updated_id,rl_user_default,rl_deleted_updated | rl_user_name_id | 4 | const | 49 | Using where; Using filesort | |
476 | +------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+ |
477 | 1 row in set (0.00 sec) |
478 | |
479 | (20:17) root@localhost:[wiki]> |
480 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated */ |
481 | -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
482 | -> FROM `reading_list`,`reading_list_entry`,`reading_list_project` |
483 | -> WHERE (rl_id = rle_rl_id) |
484 | -> AND (rle_rlp_id = rlp_id) |
485 | -> AND rle_user_id = '1000' |
486 | -> AND rl_deleted = '0' |
487 | -> AND (rle_date_updated > '20100101000000') |
488 | -> ORDER BY rle_date_updated asc,rle_id asc |
489 | -> LIMIT 1000; |
490 | +------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+ |
491 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
492 | +------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+ |
493 | | 1 | SIMPLE | reading_list_project | index | PRIMARY | rlp_project | 257 | NULL | 176 | Using index; Using temporary; Using filesort | |
494 | | 1 | SIMPLE | reading_list_entry | ref | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title | rle_user_project_title | 8 | const,wiki.reading_list_project.rlp_id | 11 | Using where | |
495 | | 1 | SIMPLE | reading_list | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY | 4 | wiki.reading_list_entry.rle_rl_id | 1 | Using where | |
496 | +------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+ |
497 | 3 rows in set (0.00 sec) |
498 | |
499 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated (paging) */ |
500 | -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted |
501 | -> FROM `reading_list`,`reading_list_entry`,`reading_list_project` |
502 | -> WHERE (rl_id = rle_rl_id) |
503 | -> AND (rle_rlp_id = rlp_id) |
504 | -> AND rle_user_id = '1000' |
505 | -> AND rl_deleted = '0' |
506 | -> AND (rle_date_updated > '20100101000000') |
507 | -> AND ((rle_date_updated > '20150101000000') OR ((rle_date_updated = '20150101000000') AND (rle_id >= 1000))) |
508 | -> ORDER BY rle_date_updated asc,rle_id asc |
509 | -> LIMIT 10; |
510 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+ |
511 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
512 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+ |
513 | | 1 | SIMPLE | reading_list_project | index | PRIMARY | rlp_project | 257 | NULL | 176 | Using index; Using temporary; Using filesort | |
514 | | 1 | SIMPLE | reading_list_entry | ref | PRIMARY,rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title | rle_user_project_title | 8 | const,wiki.reading_list_project.rlp_id | 11 | Using where | |
515 | | 1 | SIMPLE | reading_list | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY | 4 | wiki.reading_list_entry.rle_rl_id | 1 | Using where | |
516 | +------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+ |
517 | 3 rows in set (0.01 sec) |
518 | |
519 | (20:17) root@localhost:[wiki]> |
520 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByPage */ |
521 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
522 | -> FROM `reading_list`,`reading_list_entry` |
523 | -> WHERE (rl_id = rle_rl_id) |
524 | -> AND rle_user_id = '1000' |
525 | -> AND rle_rlp_id = '1' |
526 | -> AND rle_title = 'Test_1' |
527 | -> AND rl_deleted = '0' |
528 | -> AND rle_deleted = '0' |
529 | -> GROUP BY rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
530 | -> ORDER BY rle_rl_id ASC |
531 | -> LIMIT 1; |
532 | +------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+ |
533 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
534 | +------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+ |
535 | | 1 | SIMPLE | reading_list_entry | ref | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title,rle_deleted_updated | rle_user_project_title | 265 | const,const,const | 1 | Using index condition; Using where; Using temporary; Using filesort | |
536 | | 1 | SIMPLE | reading_list | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY | 4 | wiki.reading_list_entry.rle_rl_id | 1 | Using where | |
537 | +------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+ |
538 | 2 rows in set (0.00 sec) |
539 | |
540 | (20:17) root@localhost:[wiki]> |
541 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByPage (paging) */ |
542 | -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
543 | -> FROM `reading_list`,`reading_list_entry` |
544 | -> WHERE (rl_id = rle_rl_id) |
545 | -> AND rle_user_id = '1000' |
546 | -> AND rle_rlp_id = '1' |
547 | -> AND rle_title = 'Test_1' |
548 | -> AND rl_deleted = '0' |
549 | -> AND rle_deleted = '0' |
550 | -> AND (rle_rl_id >= 1000) |
551 | -> GROUP BY rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted |
552 | -> ORDER BY rle_rl_id ASC |
553 | -> LIMIT 1; |
554 | +------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+ |
555 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
556 | +------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+ |
557 | | 1 | SIMPLE | reading_list_entry | range | rle_list_deleted_title_id,rle_list_deleted_updated_id,rle_user_updated_id,rle_user_project_title,rle_deleted_updated | rle_user_project_title | 269 | NULL | 1 | Using index condition; Using where; Using temporary; Using filesort | |
558 | | 1 | SIMPLE | reading_list | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY | 4 | wiki.reading_list_entry.rle_rl_id | 1 | Using where | |
559 | +------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+ |
560 | 2 rows in set (0.00 sec) |
561 | |
562 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */ |
563 | -> rl_id |
564 | -> FROM `reading_list` |
565 | -> WHERE rl_deleted = '1' |
566 | -> AND (rl_date_updated < '20100101000000') |
567 | -> LIMIT 1000; |
568 | +------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+ |
569 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
570 | +------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+ |
571 | | 1 | SIMPLE | reading_list | range | rl_deleted_updated | rl_deleted_updated | 15 | NULL | 1 | Using where; Using index | |
572 | +------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+ |
573 | 1 row in set (0.00 sec) |
574 | |
575 | (20:17) root@localhost:[wiki]> |
576 | (20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */ |
577 | -> rle_id |
578 | -> FROM `reading_list_entry` |
579 | -> WHERE rle_deleted = '1' |
580 | -> AND (rle_date_updated < '20100101000000') |
581 | -> LIMIT 1000; |
582 | +------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+ |
583 | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
584 | +------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+ |
585 | | 1 | SIMPLE | reading_list_entry | range | rle_deleted_updated | rle_deleted_updated | 15 | NULL | 1 | Using where; Using index | |
586 | +------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+ |
587 | 1 row in set (0.00 sec) |
588 | ``` |
Paste P6427
ReadingLists query plans
ReadingLists query plans
Authored by Tgr on Dec 5 2017, 7:53 AM.
Tags
Subscribers
None