Sort lists and entries by name and last updated timestamp

Authored by Tgr on Dec 5 2017, 12:49 AM.

Description

Sort lists and entries by name and last updated timestamp

Add sort options to the read methods:

  • by name/updated for getAllLists, getListEntries and getListsByDateUpdated
  • getListEntriesByDateUpdated is always sorted by update time (to do otherwise would require an extra index and there is no use case for it)
  • getListsByPage has no human-comprehensible sorting (results are assumed to be small).

Ensure stable paging by using the primary key as secondary sortkey
everywhere.

Improve performance by aligning indexes with sort options
(and getting rid of the old offset-based paging):

  • getAllLists, getListsByDateUpdated, getListEntries, and purgeOldDeleted have a matching index (for both sort methods, in the case of the first three);
  • getListEntriesByDateUpdated has an almost matching index (both tables have to be filtered for the deleted flag so no way to cram everything in a single index).
  • getListsByPage is an efficient select with rle_user_project_title (use index to find all lists with that page, then skip where the list or the page is deleted) which then has to be sorted in a temporary table for the GROUP BY / ORDER BY. Items can appear only once per list (deleted flag notwithstanding) and list count is capped at 100 so this is a filesort on 100 rows max and the number of scanned rows is the number of lists containing the page (could be >100 due to deletions).

May have gone a bit overboard with the indexes but in previous
code review there was a preference for avoiding filesorts even
on smallish result sets and it takes this many to accomplish that.

Bug: T180402
Bug: T182053
Change-Id: Ia04a6c42ed7eca2a1c49d6ef109f0359db06ccd4

Details

Committed
TgrDec 5 2017, 8:46 PM
Parents
rERLS73bc2fecac45: Merge "Deduplicate projects into their own table"
Branches
Unknown
Tags
Unknown
References
refs/changes/25/393925/7
ChangeId
Ia04a6c42ed7eca2a1c49d6ef109f0359db06ccd4