SQL performance

Rails
1

I have a query that involves a couple of left joins and some
subqueries. Searching around on how to do this, I came across a method
that paginated from an arbitrary query:

def paginate_from_sql(model, sql, count_sql, per_page)
  plural_model_name = "@#{model.name.underscore.pluralize}".to_sym
  paginator_name = "@#{model.name.underscore}_pages".to_sym
  self.instance_variable_set(paginator_name, Paginator.new(self,

model.count_by_sql(count_sql), per_page, @params[‘page’]))
self.instance_variable_set(plural_model_name,
model.find_by_sql(sql + " LIMIT #{per_page}" + " OFFSET
#{self.instance_variable_get(paginator_name).current.to_sql[1]}"))
end

I then applied my query:

Plain english: “Select all subjects with who do
not have any calls with a call_back date scheduled and have not
received 6 calls or more”.

query = “SELECT subjects.* FROM (” +
“SELECT subjects.,count(subjects.id) AS callsum FROM (" +
"SELECT subjects. FROM subjects LEFT JOIN calls ON
calls.subject_id=subjects.id AND calls.call_back_date IS NOT NULL WHERE
calls.id IS NULL AND subjects.agreed_to_participate IS NULL AND
subjects.valid_number = ‘T’) AS subjects " +
“LEFT JOIN calls ON calls.subject_id=subjects.id GROUP BY
subjects.id) as subjects”+
" WHERE callsum < 6 AND
date_format(subjects.updated_on,’%Y-%m-%d’) < DATE_SUB(CURDATE(),
INTERVAL 1 DAY)”

count_by_sql = “SELECT COUNT(*) FROM (query)”

paginate_from_sql(Subject, query, count_by_sql, 15)

This all works fine and ouputs the correct data paginated and all.

But (there’s always a but), the performance is horrendous compared to
the previous php implementation of the same functionality. There’s
noticeable lag on each pagination or page load. Just doing a loose
count, it takes about 12s to fetch and display the data compared to
about 1 or 2s for php.

Questions:

  1. Is there a more friendly way to fetch and paginate queries like
    this?
  2. Am I missing something here that could dramatically increase
    performance as far as page rendering?