Optimizing the River page

We noticed that when viewing the river page for a user that follows many feeds, it can take quite a while to load, around 6 seconds for approximately 1000 feeds. When checking the console it was clear the river building process was the culprit:

displayTraditionalRiver: getRiver took 4.463 secs.

The query we’re looking for is made on this line on feedlanddatabase.js and it looks like so:

SELECT * FROM items WHERE flDeleted = false AND feedUrl IN (SELECT feedUrl FROM subscriptions WHERE listName='screenname') ORDER BY pubDate DESC LIMIT 175;

We initially thought the issue was the lack of LIMIT on the query or maybe the subquery to get the list of feeds but we decided to focus on the ordering (ORDER BY pubDate). After digging a little bit on the code and checking the table structure (here) for indexes we could use to make the WHERE clause efficient, we decided to go with a compose index for the columns we’re querying. We ended up with two composite index ideas:

Add index on (pubDate, feedUrl, flDeleted)

Times for that query with the new index were virtually identical, it turns out MySQL was prioritizing the flDeleted index. We choose the order of the columns based on their cardinality but since we’re querying for flDeleted=false, and it’s the only = operation, it has a higher priority.

Add index on (flDeleted, pubDate, feedUrl)

After what we learned above, we dropped that index and created a new one, which essentially brought down the time needed to around 1/7th of the original query time for the same river page:

displayTraditionalRiver: getRiver took 0.626 secs.

The performance boost was so big, Dave found it was a good idea to add it to feedland.org as well and made a note about it. It’s also even in the original instructions for the database setup.

Future improvements

We can potentially remove the other indexes if we’re sure they’re not being used by other queries. That’s because the composite index can only be used if queries uses the columns from left to right, so other queries must at least use flDeleted, or flDeleted + pubDate, or flDeleted + pubDate + feedUrl.

Things I learned in the proccess

• MySQL chooses which index to used based on a bunch of factors, one of those being the operation and another one the cardinality (check the EXPLAIN command).
• Composite indexes are used if the columns are queried from left to right (explained above).
• MySQL has a neat EXPLAIN ANALYZE tool to profile queries.

Huge thanks to Chris for helping me out debugging that query.