Daniel A. wrote in post #968711:
> Hello everyone! I've a question that I hope someone in here would be
> able to
> answer. Today I was writing a simple test for a method that takes the
> last
> "n" (where n is an integer) users that signed up on my web app.
>
> I know it's a simple method, but then I started to think about the
> optimal
> way to query the DB, and I tried different options. The first thing I
> did
> was create 1000 users through a simple:
>
> 1000.times {Factory(:student)}
>
> The difference between the first and the last records is about 2
> minutes.
>
> first created_at: 2010-12-15 20:35:41
> last created_at: 2010-12-15 20:37:51
>
> And this is what I've got on the console when using
> ActiveRecord::Base.uncached:
>
> *User.order("created_at DESC").limit(5).reverse*
> User Load (*7.4ms*) SELECT "users".* FROM "users" ORDER BY created_at
> DESC
> LIMIT 5
>
> *User.order("created_at ASC").last(5)*
> User Load (*1179.1ms*) SELECT "users".* FROM "users" ORDER BY
> created_at
> ASC
>
> *User.last(5)* *# I didn't even know I could add a parameter to last*
> User Load (*1171.2ms*) SELECT "users".* FROM "users"
>
> *User.all.last(5)*
> User Load (*1177.3ms*) SELECT "users".* FROM "users"
>
> Well, there's obviously a huge performance impact between querying the
> DB
> with the first one and the rest. There're some things I would love to
> understand.
Herewith my speculations.
>
> - If the users are created in order, this means the last user is the
> last
> that signed up. This means that the first query would have had to order
> all
> the 1000 users by date, then take only 5 and then reverse the array
> right???
> So, why is it so fast???
Probably because most of the work is being done in the database, which
is then only returning 5 records for Ruby to work with in memory. (If
you were using PostgreSQL, you could make it even faster by adding an
index on created_at. Come to think of it, maybe Rails already does
this, though I doubt it. However, MySQL is too stupid to do backward
scans of indices, IIRC.)
>
> - The second query, in this case would not have to order the users, as
> by
> default they are all ordered right? So, it would only have to take the
> last
> 5 elements of the array. But why is it slower than the first one?
Well, the DB has to do the same operation (sort all the users by date),
though this time in reverse. However, then it returns a bigger result
set to Ruby, which means that Ruby has more data to manipulate in
memory. I'm surprised that the time difference is that big, though.
>
> - The third and fourth queries, I guess load all the users in memory and
> then return only the last 5.
Yes -- but they won't do what you want. Since there's no order
specified in your query, the DB will return records in an unpredictable
order, and so taking the last 5 of them won't necessarily yield the 5
newest records.
>
> Any information about this would be really helpful. Thanks.
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
