Paste P6427

ReadingLists query plans
ActivePublic
Actions

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