Page MenuHomePhabricator
Create Task
Maniphest T182053

Improve query performance for reading lists
Closed, ResolvedPublic
Actions

Description

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

Details

SubjectRepoBranchLines +/-
Improve query plan for getListsByPagemediawiki/extensions/ReadingListswmf/1.31.0-wmf.11+6 -2
Sort lists and entries by name and last updated timestampmediawiki/extensions/ReadingListswmf/1.31.0-wmf.11+701 -287
Improve query plan for getListsByPagemediawiki/extensions/ReadingListswmf/1.31.0-wmf.10+6 -2
Sort lists and entries by name and last updated timestampmediawiki/extensions/ReadingListswmf/1.31.0-wmf.10+701 -287
Improve query plan for getListsByPagemediawiki/extensions/ReadingListsmaster+6 -2
Sort lists and entries by name and last updated timestampmediawiki/extensions/ReadingListsmaster+701 -287
Customize query in gerrit

Related Objects
Search...

Event Timeline

Tgr created this task.Dec 5 2017, 1:20 AM
Restricted Application added a project: Product-Infrastructure-Team-Backlog-Deprecated. · View Herald TranscriptDec 5 2017, 1:20 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Tgr added parent tasks: T181107: Deploy Reading Lists Service to production, T180402: Remove custom ordering from ReadingLists extension.Dec 5 2017, 1:23 AM
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

P6427 ReadingLists query plans
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

gerritbot subscribed.Dec 5 2017, 8:47 PM

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

gerritbot added a project: Patch-For-Review.Dec 5 2017, 8:47 PM
Tgr mentioned this in rERLSad1edc634adb: Sort lists and entries by name and last updated timestamp.Dec 5 2017, 8:47 PM
gerritbot added a comment.Dec 5 2017, 9:36 PM

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 mentioned this in T174126: Security review for the ReadingLists extension.Dec 6 2017, 1:30 AM
Tgr closed this task as Resolved.Dec 6 2017, 1:37 AM
Tgr claimed this task.
Tgr mentioned this in T171913: Fix technical debt in ReadingLists extension.

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

gerritbot added a comment.Dec 6 2017, 11:26 PM

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

Tgr mentioned this in rERLS1a737fe40cd4: Improve query plan for getListsByPage.Dec 6 2017, 11:29 PM
gerritbot added a comment.Dec 7 2017, 4:00 AM

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

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

ReleaseTaggerBot added a project: MW-1.31-release-notes (WMF-deploy-2017-12-12 (1.31.0-wmf.12)).Dec 7 2017, 5:00 AM
Tgr mentioned this in T181107: Deploy Reading Lists Service to production.Dec 7 2017, 8:12 AM
gerritbot added a comment.Dec 7 2017, 8:56 PM

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

gerritbot added a comment.Dec 7 2017, 8:56 PM

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

gerritbot added a comment.Dec 7 2017, 8:57 PM

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

gerritbot added a comment.Dec 7 2017, 8:57 PM

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

Tgr mentioned this in rERLS464e5df3734f: Sort lists and entries by name and last updated timestamp.Dec 7 2017, 8:58 PM
Tgr mentioned this in rERLSdc9f53e467e1: Improve query plan for getListsByPage.
Tgr mentioned this in rERLS82c6c6e828c1: Sort lists and entries by name and last updated timestamp.
Tgr mentioned this in rERLS9b2c75012aaa: Improve query plan for getListsByPage.
gerritbot added a comment.Dec 7 2017, 10:14 PM

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

gerritbot added a comment.Dec 7 2017, 10:17 PM

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

gerritbot added a comment.Dec 7 2017, 10:19 PM

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

gerritbot added a comment.Dec 7 2017, 10:23 PM

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

ReleaseTaggerBot edited projects, added MW-1.31-release-notes (WMF-deploy-2017-11-28 (1.31.0-wmf.10)); removed MW-1.31-release-notes (WMF-deploy-2017-12-12 (1.31.0-wmf.12)).Dec 7 2017, 11:00 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and 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