Paste P6427

ReadingLists query plans
ActivePublic
Actions

Authored by Tgr on Dec 5 2017, 7:53 AM.
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​```
Tgr created this paste.Dec 5 2017, 7:53 AM
Tgr edited the content of this paste. (Show Details)
Tgr updated the paste's language from remarkup to autodetect.
Tgr edited the content of this paste. (Show Details)Dec 5 2017, 7:58 AM
Tgr edited the content of this paste. (Show Details)
Tgr archived this paste.
Tgr activated this paste.Dec 5 2017, 8:08 AM
Tgr changed the title of this paste from untitled to ReadingLists query plans.Dec 5 2017, 8:51 AM
Tgr updated the paste's language from autodetect to remarkup.
Tgr edited the content of this paste. (Show Details)
Tgr added a project: Reading List Service.
Tgr edited the content of this paste. (Show Details)Dec 5 2017, 9:26 AM
Tgr edited the content of this paste. (Show Details)Dec 5 2017, 9:29 AM
Tgr edited the content of this paste. (Show Details)Dec 5 2017, 8:18 PM
Tgr edited the content of this paste. (Show Details)
Content licensed under Creative Commons Attribution-ShareAlike 3.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL