mysql - Query processing time is more..performance issue in retrieving data -

mysql - Query processing time is more..performance issue in retrieving data -

i have bit complicated model design many associations among themselves. here model design

user model

class user < activerecord::base has_many :records validates :email, presence: true end

record model

class record < activerecord::base belongs_to :user has_many :record_type_student has_many :record_type_employee has_many :record_type_other end

recordtypestudent model

class recordtypestudent < activerecord::base belongs_to :record belongs_to :user belongs_to :source end

similar model other 2 recordtypeother , recordtypeemployee

i have added index in recordtypestudent, other , employee record fast retrieval. retreiving 1000 records including 3 takes 2.4 seconds think alot. here how querying records first query

@records = record.where(:user_id => 1) @r = [] @records.each |m| if !r.record_type_students.empty? @r += r.record_type_students end if !r.record_type_other.empty? @r += r.record_type_others end if !r.record_type_employees.empty? @r += r.record_type_employees end end

the processing time low , 1000 records not sure bad queries doing or else.

application using mysql info base

completed 200 ok in 2338ms (views: 0.5ms | activerecord: 445.5ms)

it seems me creating lot of unnecessary queries. instead of pulling out records , iterating on them (which should have been done .find_each rather .each), can query individual record types right records result in in clause , done on database side. if understanding schema correctly, can same info follows (ar 4.1.1):

record = record.where(user_id: 1) r = [] r += recordtypestudent.where(record: record).to_a r += recordtypeemployee.where(record: record).to_a r += recordtypeother.where(record: record).to_a

this result in 3 queries total. can create code cleaner follows:

r = [recordtypestudent, recordtypeemployee, recordtypeother].flat_map |type| type.where(record: record.where(user_id: 1)).to_a end

if want farther drop number of queries, info via union between tables don't think that's necessary.

i guess it's here in question's text has_many in record model have specified plural arguments, otherwise won't work.

mysql ruby-on-rails activerecord database-performance