Page MenuHomePhabricator
Paste P6427

ReadingLists query plans
ActivePublic

Authored by Tgr on Dec 5 2017, 7:53 AM.
Referenced Files
F11207328: ReadingLists query plans
Dec 5 2017, 8:20 PM
F11207320: ReadingLists query plans
Dec 5 2017, 8:18 PM
F11189142: ReadingLists query plans
Dec 5 2017, 9:29 AM
F11189130: ReadingLists query plans
Dec 5 2017, 9:26 AM
F11188284: ReadingLists query plans
Dec 5 2017, 8:51 AM
F11187509:
Dec 5 2017, 7:58 AM
F11187506:
Dec 5 2017, 7:58 AM
F11187493:
Dec 5 2017, 7:53 AM
Subscribers
None
== Test setup
```
populateWithTestData.php --users 50 --lists 20 --entries 2000
UPDATE reading_list SET rl_date_updated = DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2017-10-01 14:53:27') + FLOOR(0 + (RAND() * 630720))), '%Y%m%d%H%i%s');
UPDATE reading_list_entry SET rle_date_updated = DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2017-10-01 14:53:27') + FLOOR(0 + (RAND() * 630720))), '%Y%m%d%H%i%s');
```
== Test queries
```
EXPLAIN SELECT /* ReadingListRepository::getListCount */
COUNT(*) AS `rowcount` FROM (
SELECT 1 FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted = '0'
) `tmp_count`;
EXPLAIN SELECT /* ReadingListRepository::getEntryCount */
COUNT(*) AS `rowcount` FROM (
SELECT 1 FROM `reading_list_entry`
WHERE rle_rl_id = '2'
AND rle_deleted = '0'
) `tmp_count`;
EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted = '0'
ORDER BY rl_name asc,rl_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name, paging) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted = '0'
AND ((rl_name > 'default') OR ((rl_name = 'default') AND (rl_id >= 1000)))
ORDER BY rl_name asc,rl_id asc
LIMIT 1;
EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted = '0'
ORDER BY rl_date_updated asc,rl_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated, paging) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted = '0'
AND ((rl_date_updated > '20170101000000') OR ((rl_date_updated = '20170101000000') AND (rl_id >= 1000)))
ORDER BY rl_date_updated asc,rl_id asc
LIMIT 1;
EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title) */
rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
FROM `reading_list_entry`,`reading_list_project`
WHERE (rle_rlp_id = rlp_id)
AND rle_rl_id IN ('10')
AND rle_user_id = '1000'
AND rle_deleted = '0'
ORDER BY rle_title asc,rle_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title, paging) */
rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
FROM `reading_list_entry`,`reading_list_project`
WHERE (rle_rlp_id = rlp_id)
AND rle_rl_id IN ('10')
AND rle_user_id = '1000'
AND rle_deleted = '0'
AND ((rle_title > 'Test_50') OR ((rle_title = 'Test_50') AND (rle_id >= 1000)))
ORDER BY rle_title asc,rle_id asc
LIMIT 2;
EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by updated) */
rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
FROM `reading_list_entry`,`reading_list_project`
WHERE (rle_rlp_id = rlp_id)
AND rle_rl_id IN ('10')
AND rle_user_id = '1000' AND
rle_deleted = '0'
ORDER BY rle_date_updated asc,rle_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getListEntries (multiple lists) */
rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
FROM `reading_list_entry`,`reading_list_project`
WHERE (rle_rlp_id = rlp_id)
AND rle_rl_id IN ('10')
AND rle_user_id = '1000'
AND rle_deleted = '0'
ORDER BY rle_title asc,rle_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1'
AND rl_deleted IN ('0','1')
AND (rl_date_updated > '20100101000000')
ORDER BY rl_name asc,rl_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1'
AND rl_deleted IN ('0','1')
AND (rl_date_updated > '20100101000000')
AND ((rl_name > 'Test_10') OR ((rl_name = 'Test_10') AND (rl_id >= 1000)))
ORDER BY rl_name asc,rl_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted IN ('0','1')
AND (rl_date_updated > '20100101000000')
ORDER BY rl_date_updated asc,rl_id asc
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`
WHERE rl_user_id = '1000'
AND rl_deleted IN ('0','1')
AND (rl_date_updated > '20100101000000')
AND ((rl_date_updated > '20150101000000') OR ((rl_date_updated = '20150101000000') AND (rl_id >= 1000)))
ORDER BY rl_date_updated asc,rl_id asc
LIMIT 10;
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;
EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated (paging) */
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')
AND ((rle_date_updated > '20150101000000') OR ((rle_date_updated = '20150101000000') AND (rle_id >= 1000)))
ORDER BY rle_date_updated asc,rle_id asc
LIMIT 10;
EXPLAIN SELECT /* ReadingListRepository::getListsByPage */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`,`reading_list_entry`
WHERE (rl_id = rle_rl_id)
AND rle_user_id = '1000'
AND rle_rlp_id = '1'
AND rle_title = 'Test_1'
AND rl_deleted = '0'
AND rle_deleted = '0'
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
ORDER BY rle_rl_id ASC
LIMIT 1;
EXPLAIN SELECT /* ReadingListRepository::getListsByPage (paging) */
rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
FROM `reading_list`,`reading_list_entry`
WHERE (rl_id = rle_rl_id)
AND rle_user_id = '1000'
AND rle_rlp_id = '1'
AND rle_title = 'Test_1'
AND rl_deleted = '0'
AND rle_deleted = '0'
AND (rle_rl_id >= 1000)
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
ORDER BY rle_rl_id ASC
LIMIT 1;
EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */
rl_id
FROM `reading_list`
WHERE rl_deleted = '1'
AND (rl_date_updated < '20100101000000')
LIMIT 1000;
EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */
rle_id
FROM `reading_list_entry`
WHERE rle_deleted = '1'
AND (rle_date_updated < '20100101000000')
LIMIT 1000;
```
== Indexes
```
(20:17) root@localhost:[wiki]> SHOW INDEX FROM reading_list;
+--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reading_list | 0 | PRIMARY | 1 | rl_id | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_updated_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_updated_id | 2 | rl_deleted | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_updated_id | 3 | rl_date_updated | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_updated_id | 4 | rl_id | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_name_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_name_id | 2 | rl_deleted | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_name_id | 3 | rl_name | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_deleted_name_id | 4 | rl_id | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_name_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_name_id | 2 | rl_name | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_name_id | 3 | rl_id | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_updated_id | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_updated_id | 2 | rl_deleted | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_updated_id | 3 | rl_date_updated | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 0 | rl_user_updated_id | 4 | rl_id | A | 1081 | NULL | NULL | | BTREE | | |
| reading_list | 1 | rl_user_default | 1 | rl_user_id | A | 108 | NULL | NULL | | BTREE | | |
| reading_list | 1 | rl_user_default | 2 | rl_is_default | A | 216 | NULL | NULL | | BTREE | | |
| reading_list | 1 | rl_deleted_updated | 1 | rl_deleted | A | 2 | NULL | NULL | | BTREE | | |
| reading_list | 1 | rl_deleted_updated | 2 | rl_date_updated | A | 1081 | NULL | NULL | | BTREE | | |
+--------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
20 rows in set (0.00 sec)
(20:19) root@localhost:[wiki]> SHOW INDEX FROM reading_list_entry;
+--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reading_list_entry | 0 | PRIMARY | 1 | rle_id | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_title_id | 1 | rle_rl_id | A | 1995 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_title_id | 2 | rle_deleted | A | 1995 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_title_id | 3 | rle_title | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_title_id | 4 | rle_id | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_updated_id | 1 | rle_rl_id | A | 2015 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_updated_id | 2 | rle_deleted | A | 2078 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_updated_id | 3 | rle_date_updated | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_list_deleted_updated_id | 4 | rle_id | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_updated_id | 1 | rle_user_id | A | 102 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_updated_id | 2 | rle_date_updated | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_updated_id | 3 | rle_id | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_project_title | 1 | rle_user_id | A | 102 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_project_title | 2 | rle_rlp_id | A | 18136 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_project_title | 3 | rle_title | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 0 | rle_user_project_title | 4 | rle_rl_id | A | 199500 | NULL | NULL | | BTREE | | |
| reading_list_entry | 1 | rle_deleted_updated | 1 | rle_deleted | A | 2 | NULL | NULL | | BTREE | | |
| reading_list_entry | 1 | rle_deleted_updated | 2 | rle_date_updated | A | 199500 | NULL | NULL | | BTREE | | |
+--------------------+------------+-----------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
18 rows in set (0.00 sec)
(20:19) root@localhost:[wiki]> SHOW INDEX FROM reading_list_project;
+----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reading_list_project | 0 | PRIMARY | 1 | rlp_id | A | 176 | NULL | NULL | | BTREE | | |
| reading_list_project | 0 | rlp_project | 1 | rlp_project | A | 176 | NULL | NULL | | BTREE | | |
+----------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
```
== Plans
```
(20:16) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListCount */
-> COUNT(*) AS `rowcount` FROM (
-> SELECT 1 FROM `reading_list`
-> WHERE rl_user_id = '1000'
-> AND rl_deleted = '0'
-> ) `tmp_count`;
se+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
| 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 |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
1 row in set (0.01 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getEntryCount */
-> COUNT(*) AS `rowcount` FROM (
-> SELECT 1 FROM `reading_list_entry`
l -> WHERE rle_rl_id = '2'
-> AND rle_deleted = '0'
-> ) `tmp_count`;
+------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+
| 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 |
+------+-------------+--------------------+------+---------------------------------------------------------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]>
L(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
_ -> FROM `reading_list`
-> WHERE rl_user_id = '1000'
-> AND rl_deleted = '0'
-> ORDER BY rl_name asc,rl_id asc
' -> LIMIT 1000;
r+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
| 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 |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by name, paging) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
d -> FROM `reading_list`
l -> WHERE rl_user_id = '1000'
e -> AND rl_deleted = '0'
-> AND ((rl_name > 'default') OR ((rl_name = 'default') AND (rl_id >= 1000)))
0 -> ORDER BY rl_name asc,rl_id asc
'1 -> LIMIT 1;
ND+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
| 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 |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated) */
0 -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
-> FROM `reading_list`
-> WHERE rl_user_id = '1000'
-> AND rl_deleted = '0'
-> ORDER BY rl_date_updated asc,rl_id asc
-> LIMIT 1000;
d+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
| 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 |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
1 row in set (0.01 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getAllLists (sort by updated, paging) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
_ -> FROM `reading_list`
r -> WHERE rl_user_id = '1000'
l -> AND rl_deleted = '0'
-> AND ((rl_date_updated > '20170101000000') OR ((rl_date_updated = '20170101000000') AND (rl_id >= 1000)))
-> ORDER BY rl_date_updated asc,rl_id asc
-> LIMIT 1;
e+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
| 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 |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]>
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title) */
-> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
r -> FROM `reading_list_entry`,`reading_list_project`
e -> WHERE (rle_rlp_id = rlp_id)
d -> AND rle_rl_id IN ('10')
t -> AND rle_user_id = '1000'
-> AND rle_deleted = '0'
-> ORDER BY rle_title asc,rle_id asc
e -> LIMIT 1000;
0+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
| 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 |
| 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | |
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
2 rows in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by title, paging) */
-> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
-> FROM `reading_list_entry`,`reading_list_project`
r -> WHERE (rle_rlp_id = rlp_id)
-> AND rle_rl_id IN ('10')
g -> AND rle_user_id = '1000'
= -> AND rle_deleted = '0'
s -> AND ((rle_title > 'Test_50') OR ((rle_title = 'Test_50') AND (rle_id >= 1000)))
e -> ORDER BY rle_title asc,rle_id asc
-> LIMIT 2;
GROUP BY+------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
| 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 |
| 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | |
+------+-------------+----------------------+--------+------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
2 rows in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (sort by updated) */
c -> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
-> FROM `reading_list_entry`,`reading_list_project`
-> WHERE (rle_rlp_id = rlp_id)
-> AND rle_rl_id IN ('10')
i -> AND rle_user_id = '1000' AND
, -> rle_deleted = '0'
i -> ORDER BY rle_date_updated asc,rle_id asc
-> LIMIT 1000;
= rle_rl_id)
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+
| 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 |
| 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | |
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+------------------------------------+------+-------------+
2 rows in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntries (multiple lists) */
-> rle_id,rle_rl_id,rlp_project,rle_title,rle_date_created,rle_date_updated,rle_deleted
0 -> FROM `reading_list_entry`,`reading_list_project`
-> WHERE (rle_rlp_id = rlp_id)
a -> AND rle_rl_id IN ('10')
de -> AND rle_user_id = '1000'
C -> AND rle_deleted = '0'
/ -> ORDER BY rle_title asc,rle_id asc
-> LIMIT 1000;
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
| 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 |
| 1 | SIMPLE | reading_list_project | eq_ref | PRIMARY | PRIMARY | 4 | wiki.reading_list_entry.rle_rlp_id | 1 | |
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------------------+---------------------------+---------+------------------------------------+------+-------------+
2 rows in set (0.00 sec)
(20:17) root@localhost:[wiki]>
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */
0 -> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
g -> FROM `reading_list`
d -> WHERE rl_user_id = '1'
e_ -> AND rl_deleted IN ('0','1')
-> AND (rl_date_updated > '20100101000000')
-> ORDER BY rl_name asc,rl_id asc
-> LIMIT 1000;
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
| 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 |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
-> FROM `reading_list`
-> WHERE rl_user_id = '1'
-> AND rl_deleted IN ('0','1')
-> AND (rl_date_updated > '20100101000000')
-> AND ((rl_name > 'Test_10') OR ((rl_name = 'Test_10') AND (rl_id >= 1000)))
-> ORDER BY rl_name asc,rl_id asc
-> LIMIT 1000;
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
| 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 |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
-> FROM `reading_list`
-> WHERE rl_user_id = '1000'
-> AND rl_deleted IN ('0','1')
-> AND (rl_date_updated > '20100101000000')
-> ORDER BY rl_date_updated asc,rl_id asc
-> LIMIT 1000;
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
| 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 |
+------+-------------+--------------+------+--------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByDateUpdated (order by name, paging) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
-> FROM `reading_list`
-> WHERE rl_user_id = '1000'
-> AND rl_deleted IN ('0','1')
-> AND (rl_date_updated > '20100101000000')
-> AND ((rl_date_updated > '20150101000000') OR ((rl_date_updated = '20150101000000') AND (rl_id >= 1000)))
-> ORDER BY rl_date_updated asc,rl_id asc
-> LIMIT 10;
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
| 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 |
+------+-------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]>
(20:17) 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)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListEntriesByDateUpdated (paging) */
-> 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')
-> AND ((rle_date_updated > '20150101000000') OR ((rle_date_updated = '20150101000000') AND (rle_id >= 1000)))
-> ORDER BY rle_date_updated asc,rle_id asc
-> LIMIT 10;
+------+-------------+----------------------+--------+----------------------------------------------------------------------------------------------------------+------------------------+---------+----------------------------------------+------+----------------------------------------------+
| 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 | 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 |
| 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.01 sec)
(20:17) root@localhost:[wiki]>
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByPage */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
-> FROM `reading_list`,`reading_list_entry`
-> WHERE (rl_id = rle_rl_id)
-> AND rle_user_id = '1000'
-> AND rle_rlp_id = '1'
-> AND rle_title = 'Test_1'
-> AND rl_deleted = '0'
-> AND rle_deleted = '0'
-> 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
-> ORDER BY rle_rl_id ASC
-> LIMIT 1;
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
| 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 |
| 1 | SIMPLE | reading_list | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY | 4 | wiki.reading_list_entry.rle_rl_id | 1 | Using where |
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
(20:17) root@localhost:[wiki]>
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::getListsByPage (paging) */
-> rl_id,rl_is_default,rl_name,rl_description,rl_color,rl_image,rl_icon,rl_date_created,rl_date_updated,rl_deleted
-> FROM `reading_list`,`reading_list_entry`
-> WHERE (rl_id = rle_rl_id)
-> AND rle_user_id = '1000'
-> AND rle_rlp_id = '1'
-> AND rle_title = 'Test_1'
-> AND rl_deleted = '0'
-> AND rle_deleted = '0'
-> AND (rle_rl_id >= 1000)
-> 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
-> ORDER BY rle_rl_id ASC
-> LIMIT 1;
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
| 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 |
| 1 | SIMPLE | reading_list | eq_ref | PRIMARY,rl_deleted_updated | PRIMARY | 4 | wiki.reading_list_entry.rle_rl_id | 1 | Using where |
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */
-> rl_id
-> FROM `reading_list`
-> WHERE rl_deleted = '1'
-> AND (rl_date_updated < '20100101000000')
-> LIMIT 1000;
+------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | reading_list | range | rl_deleted_updated | rl_deleted_updated | 15 | NULL | 1 | Using where; Using index |
+------+-------------+--------------+-------+--------------------+--------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
(20:17) root@localhost:[wiki]>
(20:17) root@localhost:[wiki]> EXPLAIN SELECT /* ReadingListRepository::purgeOldDeleted */
-> rle_id
-> FROM `reading_list_entry`
-> WHERE rle_deleted = '1'
-> AND (rle_date_updated < '20100101000000')
-> LIMIT 1000;
+------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | reading_list_entry | range | rle_deleted_updated | rle_deleted_updated | 15 | NULL | 1 | Using where; Using index |
+------+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
```

Event Timeline

Tgr edited the content of this paste. (Show Details)
Tgr updated the paste's language from remarkup to autodetect.
Tgr edited the content of this paste. (Show Details)
Tgr archived this paste.
Tgr changed the title of this paste from untitled to ReadingLists query plans.Dec 5 2017, 8:51 AM
Tgr updated the paste's language from autodetect to remarkup.
Tgr edited the content of this paste. (Show Details)
Tgr added a project: Reading List Service.
Tgr edited the content of this paste. (Show Details)