News for hackers. Stuff that matters.
Re:How to take advantage of Redis just adding it to your stack by randito on Tuesday June 28, 2011 @11:40AM (#2705796)
In your first example, you use redis to cache the id's of the latests comments, with a fallback to SQL in order to populate the list. However, you still need to call the DB to load the comments. I don't see the gain here.

Yes, you've replaced a "select * from comments order by created_at limit 10" with a "select * from comments where id in (list_of_ids_from_redis)".

Wouldn't you cache the comment models in a top-10 list?

Re:How to take advantage of Redis just adding it to your stack by antirez on Tuesday June 28, 2011 @12:37PM (#2706050)
Often the DB will show unacceptable time to reply to the ORDER BY stuff but will fetch comments by single ID without problems. But when that second part is a problem as well it is a good idea to use Redis as a "vulgar" cache for items as well, so that the recent stuff are probably into a Redis hash and you can fetch everything with a single pipelined MULTI/EXEC call (and fetch all the items returned as nils from the DB).
Re:How to take advantage of Redis just adding it to your stack by rbranson on Wednesday June 29, 2011 @12:43AM (#2708428)
A composite index will work wonders for this situation. EXPLAIN is your friend.
Re:How to take advantage of Redis just adding it to your stack by courtewing on Tuesday June 28, 2011 @11:45AM (#2705821)
When dealing with a lot of data, the latter SQL query would potentially be significantly faster than the former. If your goal is simply to show a top-10, then caching the entire results would be a great idea, but if you're goal (like in the article) is to make retrieving the first 5000 comments quickly, then this implementation is pretty solid.
Re:How to take advantage of Redis just adding it to your stack by HarrisonFisk on Tuesday June 28, 2011 @11:56AM (#2705869)
I don't think the latter SQL would be significantly faster assuming the appropriate index on created_at.

The database can read the last 10 via the index directly and they would all most likely be on the same index page. Assuming any sort of normal caching, this would be at most 1-3 random reads and most likely none since I presume that the created_at index is generally be written in ascending order.

Once that step is done, it is essentially identical to your IN statement you did.

Re:How to take advantage of Redis just adding it to your stack by WALoeIII on Tuesday June 28, 2011 @01:17PM (#2706235)
this is MySQL specific, but we do this and it is a HUGE win for us

MySQL doesn't support descending indexes, so for a large class of problems you will have to scan the entire index to find the last 10 items, especially when sorting by items in two directions. This is really slow when you have one hundred million entries (a huge events table). Looking up by primary key is very very fast in MySQL with InnoDB. If you profile this query you can see MySQL spends most of its time figuring out the IDs, and almost no time reading them back to you. Using Redis in this manner is very memory efficent, easy to update, and gets you 95%+ of the potential performance gains. It means we don't have to keep Redis up to date with edits or changes, because the PKs are set in stone.

Re:How to take advantage of Redis just adding it to your stack by dolinsky on Tuesday June 28, 2011 @07:51PM (#2707863)
If you replace "especially when sorting by items in two directions" with "only when sorting by more than one item in different directions" above when talking about composite keys, then yes that's a factual statement of MySQL and something I believe Postgres supports.

The same composite index in MySQL can be used to do ASC-ASC and DESC-DESC sorts but cannot be used to perform an ASC-DESC sort.

Re:How to take advantage of Redis just adding it to your stack by fendale on Tuesday June 28, 2011 @05:02PM (#2707315)
Can mysql really not use an index to do a top / bottom 10 query? In Oracle it can do a top n query avoiding a sort very quickly on massive datasets given an index on the sort column. Being that is a common pattern in web apps, which is probably mysqls target market you'd think that would be high up the dev priority list!
Re:How to take advantage of Redis just adding it to your stack by rbranson on Wednesday June 29, 2011 @12:42AM (#2708425)
Uhm, MySQL indexes efficiently sort in both directions. You can use a composite index to achieve an ordered set with a set of while conditions, even with millions of rows. As long as the app is only fetching like 10 rows, it should be lightning fast.