Page MenuHomePhabricator

Improve query performance for reading lists
Closed, ResolvedPublic

Description

  • avoid filesort / temporary tables as much as possible
  • avoid offet-based paging

Details

Related Gerrit Patches:
mediawiki/extensions/ReadingLists : wmf/1.31.0-wmf.11Improve query plan for getListsByPage
mediawiki/extensions/ReadingLists : wmf/1.31.0-wmf.11Sort lists and entries by name and last updated timestamp
mediawiki/extensions/ReadingLists : wmf/1.31.0-wmf.10Improve query plan for getListsByPage
mediawiki/extensions/ReadingLists : wmf/1.31.0-wmf.10Sort lists and entries by name and last updated timestamp
mediawiki/extensions/ReadingLists : masterImprove query plan for getListsByPage
mediawiki/extensions/ReadingLists : masterSort lists and entries by name and last updated timestamp

Event Timeline

Tgr created this task.Dec 5 2017, 1:20 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Tgr added a comment.Dec 5 2017, 1:45 AM
  • https://gerrit.wikimedia.org/r/#/c/393925/ gets rid of all OFFSETs and adds a bunch of more targeted indexes, but still a number of filesorts/temp tables reamin
  • MediaWiki support for convenient performance checking: https://gerrit.wikimedia.org/r/#/c/393503/
  • script for generating test data (to avoid the query optimizer choosing otherwise costly plans when it sees there is very little data): https://gerrit.wikimedia.org/r/#/c/394728/
  • production uses MariaDB 10.0; vagrant uses MySQL 5.6; the stretch-migration branch for vagrant uses MariaDB 10.1.
    • debugged/fixed some issues with the migration branch and tried to downgrade the MariaDB version. Unfortunately stretch does not support 10.0 anymore; I tried to forward-port the jessie package but it was well above my package management abilities.
    • patch for using MariaDB 10.0 on vagrant master: https://gerrit.wikimedia.org/r/395170 (Turning it into a proper role would be difficult and T51652: Vagrant should use MariaDB is already part of the stretch migration so not worth the effort.)
Tgr added a comment.EditedDec 5 2017, 8:46 PM

The current query plans are in

1== Test setup
2```
3populateWithTestData.php --users 50 --lists 20 --entries 2000
4UPDATE 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');
5UPDATE 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```
11EXPLAIN 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`;
17EXPLAIN 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
24EXPLAIN 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;
31EXPLAIN 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;
39EXPLAIN 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;
46EXPLAIN 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
55EXPLAIN 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;
64EXPLAIN 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;
74EXPLAIN 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;
83EXPLAIN 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
93EXPLAIN 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;
101EXPLAIN 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;
110EXPLAIN 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;
118EXPLAIN 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
128EXPLAIN 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;
138EXPLAIN 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
150EXPLAIN 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
163EXPLAIN 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;
176EXPLAIN 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
183EXPLAIN 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+--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
21820 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+--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
24318 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+----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2522 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`;
263se+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
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+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
2681 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`
273l -> 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+------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+
2811 row in set (0.00 sec)
282
283(20:17) root@localhost:[wiki]>
284L(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;
292r+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
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+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
2971 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
301d -> FROM `reading_list`
302l -> WHERE rl_user_id = '1000'
303e -> AND rl_deleted = '0'
304 -> AND ((rl_name > 'default') OR ((rl_name = 'default') AND (rl_id >= 1000)))
3050 -> ORDER BY rl_name asc,rl_id asc
306'1 -> LIMIT 1;
307ND+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
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+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
3121 row in set (0.00 sec)
313
314(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated) */
3150 -> 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;
321d+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
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+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
3261 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'
332l -> 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;
336e+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
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+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
3411 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
346r -> FROM `reading_list_entry`,`reading_list_project`
347e -> WHERE (rle_rlp_id = rlp_id)
348d -> AND rle_rl_id IN ('10')
349t -> AND rle_user_id = '1000'
350 -> AND rle_deleted = '0'
351 -> ORDER BY rle_title asc,rle_id asc
352e -> LIMIT 1000;
3530+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
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+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
3592 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`
364r -> WHERE (rle_rlp_id = rlp_id)
365
366 -> AND rle_rl_id IN ('10')
367g -> AND rle_user_id = '1000'
368= -> AND rle_deleted = '0'
369s -> AND ((rle_title > 'Test_50') OR ((rle_title = 'Test_50') AND (rle_id >= 1000)))
370e -> 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+------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
3792 rows in set (0.00 sec)
380
381(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by updated) */
382c -> 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')
386i -> AND rle_user_id = '1000' AND
387, -> rle_deleted = '0'
388i -> 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+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+
3972 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
4010 -> FROM `reading_list_entry`,`reading_list_project`
402 -> WHERE (rle_rlp_id = rlp_id)
403a -> AND rle_rl_id IN ('10')
404de -> AND rle_user_id = '1000'
405C -> 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+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
4142 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) */
4180 -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
419g -> FROM `reading_list`
420d -> WHERE rl_user_id = '1'
421e_ -> 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+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
4301 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+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
4461 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+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
4611 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+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
4771 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+------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+
4973 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+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+
5173 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+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
5382 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+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
5602 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+------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+
5731 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+------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+
5871 row in set (0.00 sec)
588```

Comments:

getAllLists: usually ends up with a good plan but sometimes results in a filesort - could not pin down exactly when. Since the same query structure can result in plans with or without filesort, I will assume that it's just the optimizer deciding that fully using the index is not worth it when there is not enough data (even though I used two million rows of test data) so it's not a problem.

getListsByDateUpdated: added two dedicated indexes for this (so now there are 13 indexes on the two main tables :/ ). The problem is that getAllLists needs to filter on rl_deleted and getListsByDateUpdated doesn't; I hoped rl_deleted IN (0,1) could be handled with an index merge but the ORDER BY doesn't include rl_deleted so that won't work. Alternatively this could be handled by not having rl_deleted in the index and making getAllLists go through a few extra rows, or by including rl_deleted in ORDER BY / continuation; something to come back to later.

getListEntries: filesorts when getting the entries of multiple lists. Probably the same issue as above: the list ID is included in the condition but not in the ORDER BY. The REST endpoints never request more than one list so meh. Maybe worth fixing later.

getListEntriesByDateUpdated: this ends up with a completely crazy plan:

(20:19) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated */  
    ->     rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted  
    ->     FROM `reading_list`,`reading_list_entry`,`reading_list_project`    
    ->     WHERE (rl_id = rle_rl_id) 
    ->       AND (rle_rlp_id = rlp_id) 
    ->       AND rle_user_id = '1000' 
    ->       AND rl_deleted = '0' 
    ->       AND (rle_date_updated > '20100101000000')  
    ->     ORDER BY rle_date_updated asc,rle_id asc 
    ->     LIMIT 1000;
+------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+
| id   | select_type | table                | type   | possible_keys                                                                                    | key                    | key_len | ref                                    | rows | Extra                                        |
+------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | reading_list_project | index  | PRIMARY                                                                                          | rlp_project            | 257     | NULL                                   |  176 | Using index; Using temporary; Using filesort |
|    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                                  |
|    1 | SIMPLE      | reading_list         | eq_ref | PRIMARY,rl_deleted_updated                                                                       | PRIMARY                | 4       | wiki.reading_list_entry.rle_rl_id      |    1 | Using where                                  |
+------+-------------+----------------------+--------+--------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

When I force rle_user_updated_id I get a sane plan, with more rows:

(20:35) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated */  
    ->     rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted  
    ->     FROM `reading_list`,`reading_list_entry` use index (rle_user_updated_id),`reading_list_project`    
    ->     WHERE (rl_id = rle_rl_id) 
    ->       AND (rle_rlp_id = rlp_id) 
    ->       AND rle_user_id = '1000' 
    ->       AND rl_deleted = '0' 
    ->       AND (rle_date_updated > '20100101000000')  
    ->     ORDER BY rle_date_updated asc,rle_id asc 
    ->     LIMIT 1000;
+------+-------------+----------------------+--------+----------------------------+---------------------+---------+------------------------------------+------+-----------------------+
| id   | select_type | table                | type   | possible_keys              | key                 | key_len | ref                                | rows | Extra                 |
+------+-------------+----------------------+--------+----------------------------+---------------------+---------+------------------------------------+------+-----------------------+
|    1 | SIMPLE      | reading_list_entry   | range  | rle_user_updated_id        | rle_user_updated_id | 18      | NULL                               | 4158 | Using index condition |
|    1 | SIMPLE      | reading_list         | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY             | 4       | wiki.reading_list_entry.rle_rl_id  |    1 | Using where           |
|    1 | SIMPLE      | reading_list_project | eq_ref | PRIMARY                    | PRIMARY             | 4       | wiki.reading_list_entry.rle_rlp_id |    1 |                       |
+------+-------------+----------------------+--------+----------------------------+---------------------+---------+------------------------------------+------+-----------------------+
3 rows in set (0.00 sec)

so I'm going to assume that this is again the optimizer doing weird optimization on a small data set.

getListsByPage: uses temporary + filesort. This is unavoidable and should be OK performance-wise; the commit summary of ad1edc63 has the details. seems fixed after 1a737fe40cd4

Change 393925 had a related patch set uploaded (by Gergő Tisza; owner: Gergő Tisza):
[mediawiki/extensions/ReadingLists@master] Sort lists and entries by name and last updated timestamp

https://gerrit.wikimedia.org/r/393925

Change 393925 merged by jenkins-bot:
[mediawiki/extensions/ReadingLists@master] Sort lists and entries by name and last updated timestamp

https://gerrit.wikimedia.org/r/393925

Tgr moved this task from Backlog to Coming soon on the Reading List Service board.Dec 6 2017, 12:33 AM
Tgr closed this task as Resolved.Dec 6 2017, 1:37 AM
Tgr claimed this task.

Added the remaining FIXMEs (which do not have significant production impact) to T171913: Fix technical debt in ReadingLists extension.

Change 395885 had a related patch set uploaded (by Gergő Tisza; owner: Gergő Tisza):
[mediawiki/extensions/ReadingLists@master] Improve query plan for getListsByPage

https://gerrit.wikimedia.org/r/395885

Change 395885 merged by jenkins-bot:
[mediawiki/extensions/ReadingLists@master] Improve query plan for getListsByPage

https://gerrit.wikimedia.org/r/395885

Change 396108 had a related patch set uploaded (by Gergő Tisza; owner: Gergő Tisza):
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.10] Sort lists and entries by name and last updated timestamp

https://gerrit.wikimedia.org/r/396108

Change 396113 had a related patch set uploaded (by Gergő Tisza; owner: Gergő Tisza):
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.10] Improve query plan for getListsByPage

https://gerrit.wikimedia.org/r/396113

Change 396116 had a related patch set uploaded (by Gergő Tisza; owner: Gergő Tisza):
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.11] Sort lists and entries by name and last updated timestamp

https://gerrit.wikimedia.org/r/396116

Change 396121 had a related patch set uploaded (by Gergő Tisza; owner: Gergő Tisza):
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.11] Improve query plan for getListsByPage

https://gerrit.wikimedia.org/r/396121

Change 396108 merged by jenkins-bot:
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.10] Sort lists and entries by name and last updated timestamp

https://gerrit.wikimedia.org/r/396108

Change 396113 merged by jenkins-bot:
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.10] Improve query plan for getListsByPage

https://gerrit.wikimedia.org/r/396113

Change 396116 merged by jenkins-bot:
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.11] Sort lists and entries by name and last updated timestamp

https://gerrit.wikimedia.org/r/396116

Change 396121 merged by jenkins-bot:
[mediawiki/extensions/ReadingLists@wmf/1.31.0-wmf.11] Improve query plan for getListsByPage

https://gerrit.wikimedia.org/r/396121