3 ms·

I agree - that query looks like it would benefit from optimization. At first glance, it seems to me like the query would be much faster if joined activity_type

I agree - that query looks like it would benefit from optimization. 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.

matt4077 9y ago
I don't quite get your idea about the case statement? This is a straight-up condition, and the database only ever gets something like id=12312. In fact there isn't even a to_i involved–ruby will do a .to_s, on something that is guaranteed to be an integer. 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.
- slantyyz 9y ago
  >> I don't quite get your idea about the case statement? 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 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.

matt4077 9y ago

I don't quite get your idea about the case statement? This is a straight-up condition, and the database only ever gets something like id=12312. In fact there isn't even a to_i involved–ruby will do a .to_s, on something that is guaranteed to be an integer. 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.

slantyyz 9y ago
>> I don't quite get your idea about the case statement? 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 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.