Adam Coldrick 63a003af7f Optimise the Story browsing query
The `story_get_all` function currently constructs a query which perfoms
very poorly, making search in StoryBoard painful to the point of being
almost unusable with only ~30000 stories in the database. There are a
number of reasons for the poor performance, but the most impactful is
the fact that we join two subqueries, one of which contains the entire
stories table, and the other which contains a filtered list of story
IDs to implement most of the filtering.

This commit removes all of the subquerying, and just does simple
filtering in a single WHERE clause. Basic comparison of the two queries
shows around an 85% - 90% speedup in the execution time using the new
approach, from ~100ms for the current query, and ~10-15ms for the new
query (using a test dataset containing 10000 stories).

This approach adds a bit of code complexity around filtering stories by
story status, but this is a cost worth paying for the performance
improvement. The new approach will also allow extra flexibility in the
meaning of the story statuses, making it easier to have per-project
statuses for example.

Story: 2005845
Task: 33627
Change-Id: I7c19372506017726e4fc61ffb09d3cfb9c6b36d3
2020-09-11 12:27:55 +01:00
.env Add docker-compose based dev setup instructions 2019-03-25 13:06:06 +00:00
docker-compose.yml Optimise the Story browsing query 2020-09-11 12:27:55 +01:00 Use the official Swift docker image for testing 2019-11-16 20:59:38 +00:00
storyboard.conf Regular expression support for CORS and OAuth ACLs 2019-10-25 22:22:51 +00:00