== 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');
```
== Plans
```
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','11')
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','11')
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','11')
AND rle_user_id = '1000' AND
rle_deleted = '0'
ORDER BY rle_date_updated 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;
```
== Results
```
+------+-------------+--------------------+--------+----------------------------------------------------------------------------------------------------
----------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
| 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_list_updated_deleted_id,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 |
+------+-------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-----------------------------------+------+---------------------------------------------------------------------+
```