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 <subject conditions> 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?
