Company.count taking over 3 seconds for DB of over 500K

rubyonrails-talk
1

Why would: Company.count

Take 3.5s consistently to process... if I pass conditions that limit the size to about 5K the time it takes to count is milliseconds. What is up with that jerry?

2

Hey John,

Company.count runs a "select count(*) from company"(or some db
specific variation) - which by nature must scan every row of your
database.

This is something you may be able to tune from your databases
perspective - try a google search on "select count" and your database
name.

Cheers, Jodi

3

Keyan, using that method, I think he'd be instantiating 1/2 million objects.

You'd be better off finding a better way to have the db count for you.

J

4

Hey,

how long does it take to run this query directly against the DB?

select count(*) as count_all from companies

I don't have a table with 500K records handy but I do have one with
50K and Model.count is instantaneous for me.

Here's a guess: is your Company model using STI? If it is, and you
haven't indexed your inheritance column then things might get slow
because the query is actually something like:

select count(*) as count_all from your_sti_table where type = 'Company'

HTH, Trevor

5

Keynan Pratt wrote:

count a result set instead of the model.

comps = Company.find(:all) comps.count

#best guess your executing multiple queries as part of count (2n+1)

Responding to whoever said that would create an object for every record in the table:

ActiveRecord does everything else automagically, so can't it find() a cursor and then decline to populate this until we start calling .each?

I know that's probably a little bit too much magic. Nothing else around here seems to value streaming over buffering. But cursors might find their record counts almost as cheaply as a database COUNT(*) would too, right?

6

Very interesting... I am using innoDB. Maybe that is why I am having problems. Plus I probably don't have enough RAM on my dev box. hmmm... thanks :slight_smile:

7

Hi John, try 'select count(id) from....' instead of 'select count(*)...'. I'm not sure about MySQL, but other databases (sybase and db2) will select all columns from the row to do the count, which means a full table scan. If you only scan the id column, it will be faster. If it is indexed, it will be a lot faster.

good luck! Adam

8

Adam Greene wrote:

try 'select count(id) from....' instead of 'select count(*)...'. I'm not sure about MySQL, but other databases (sybase and db2) will select all columns from the row to do the count, which means a full table scan. If you only scan the id column, it will be faster. If it is indexed, it will be a lot faster.

(I don't know if someone asked this, but) if you have a billionty-one extra tables all joined in with rampant has_many directives, would a lowly .count() accidentally traverse them all?