SQL performance

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?

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs