I need to speed up a count of apearances of a distinct category of
entities in a table. I get the categories with the following command:
@asset_classes = ActiveRecord::Base.connection.select_values “SELECT
DISTINCT CLASS FROM SymbolsMatch_MASTER ORDER BY CLASS”
Now I need to create a list of this categories together with the number
of apearance in the table. Momentarily I use this syntax but it is VERY
slow.
<% for asset_class in @asset_classes do %>
<% count = DeviceToUpdate.find(:all, :conditions => ["status like
'moreinfo' and CLIENT_STATUS is NULL and CLASS like ?",asset_class ]
).count
if count > 0 %>
number
of apearance in the table. Momentarily I use this syntax but it is
VERY
slow.
If you just want to count items then use DeviceToUpdate.count …
which will use an sql count rather than fetching the entire collection
into memory and counting that (although arrays don’t have a count
method so i’m not sure how the code below is working at all). An
appropriate index would also help counting, and/or you could cache the
result
If you just want to count items then use DeviceToUpdate.count …
You mean like this?
count = DeviceToUpdate.count(:conditions => [“status like ‘moreinfo’
and
CLIENT_STATUS is NULL and CLASS like ?”,asset_class ] )
Also keep in mind that in MySQL InnoDB “like” searches are unindexed
and notoriously slow in the first place.
Compare it to having the find the number of occurrences of the word
“rails” in a book. One book offers no index at the back of the book,
so you have to manually go over every page finding the word (MySQL
LIKE search), while the other does have one and you only need to count
the pages it appears on listed next to it (Fulltext indexer)