A query had to go
activityclubapp.tumblr.com
activityclubapp.tumblr.com
Then, I'd try an INDEX(user_id, activity_type_id) on activities. InnoDB only uses a single index per table IIRC, and that would mean reading the entire table every time.
Lastly, and I'm not sure if this makes a difference, but it feels wrong to specify the activities.user_id = #{id} in the ON clause. It really should be in WHERE... That may not change anything, or it could cause some really bad cross products currently being produced as intermediate results.
Haven't really used mysql in recent years (6+), but I remember once optimizing a query with about 8 inner joins, grouping and a very long where statement. After setting up all the indexes it still didn't perform as we hoped. Moving some of the conditions up into the ON parts just like in that blog post drastically improved performance. It seemed like in the naive approach, mysql really did all the joins first and only in the end started filtering. I was quite surprised that wasn't handled by the query optimizer, but maybe that has improved since then.
The author writes about how he used this system to solve a case of a slow query. However, this is really sort of the wrong query to illustrate why we built this. It's more of a "oh hey look since we already have this data super fast in this other system, lets just use it."
What the system does is allow us to arbitrarily summarize at whatever granularity we want, any second, to any second.
For example we can ask it to summarize January 4th at 11:22:02 to March 1st at 16:22:09, and so however many writes happened during that time at any second are tallied up very quick, because of the way the data is stored. So even though lets say during this 3 month~ period you have 10,000,000 writes for one user some that say "add 3" or "remove 4", you will only query at a second granularity around the edges, and the middle you will query whole day, or month buckets.
Another way of explaining the point of this system is that it allows us to really quickly (well under 1ms) summarize any data in the past N days (N usually equals 120) from any start to any stop point we want, using any granularity we want, and we can do it concurrently and generate this for 1000 users at a time and it all can happen in 30-50ms. Considering that the underlying data is recorded at second granularity, and can be retrieved at that granularity and automatically expired when this granularity is not needed, redis is the right tool for the job here. To accomplish this in pure sql you would have to log all writes you make to the table, and query this change logging table in a similar way. So not only would this add a lot of overhead to the database as a whole, but a lot of churn to remove the lower granularity when its not needed.
We will be polishing the system more over time, and more blog posts will come out when we release some of the client code ;)
That might also make the join less painful, right? Additionally, I think there might be a logic issue with the query itself, but I can't fully wrap my head around it.
Also, activities.user_id = #{id} smells like an SQL injection. But maybe that's just because of the simplification for the blog entry, or id not user chose-able. Still.
Well you can't get around that considering that `freq` is the result of aggregation. Therefore you do have to "take all available results". Or you could keep a tally for all users and refresh it periodically.
At first glance, it seems to me like the query would be much faster if joined activity_types and activities were filtered subqueries instead.
Also, activities.user_id = #{id} might be better handled with an SQL CASE statement as opposed to using the .to_i conversion in Ruby.
I have similar feelings about subqueries. This is a rather elementary standard join query, and I always felt subqueries were only tacked onto SQL because people had trouble understanding joins.
The case statement would only be there to set it to a numeric 0 when it's an unusual value like an apostrophe - which is what the OP said it did in a comment somewhere else in this discussion (https://news.ycombinator.com/item?id=14499589).
>> I have similar feelings about subqueries. This is a rather elementary standard join query, and I always felt subqueries were only tacked onto SQL because people had trouble understanding joins.
Not exactly sure what you meant by similar feelings (did you mean you also don't get the idea about it?). In the absence of knowing MySQL's execution plan for the OP's query, I tend to think using subqueries that reduce the rows of activity_types and activities would make the join, aggregation and ordering faster (assuming the tables are already properly indexed). I've seen this reduce query times on other database engines for similar types of queries.
Indices are based on the same logic, i. e. "99% are reads, let's try to do more work on writes".
Redis is great. But to be honest: so is MySQL. If you give MySQL enough RAM and create the right indices, you can easily get to 6-digit numbers of queries per second.
Unless MySQL is smart enough to do something like "implicit grouping" where if you group on the primary key it knows that all other columns of that table could also be included in the group... in which case I really should give MySQL another chance!
https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.ht...