Here are my table structure query and expalin query result. I have indexed the fields whatever they needed to optimize, but it selectes all the rows in one of the table (experiencetypeapplication) which has 69097 rows. And because of it slows down the search query and try to copy the query in tmp file and locked it in server.
Can anyone figure out the what is wrong is here?
Tables:
1. Jobapplication- Primary Key(JobApplicationID)
2. experiencetypeapplication
fields: JobapplicationID, ExperienceTypeID
index: both field indexed in single(index order is JobapplicationID,ExperienceTypeID)
3. employmenttypeapplication
fields: JobapplicationID, EmploymentTypeID
index: both field indexed in single(index order is JobapplicationID,EmploymentTypeID)
Query:
explain select jobapplication.JobApplicationID from employmenttypeapplication,experiencetypeapplication, jobapplication where employmenttypeapplication.EmploymentTypeID in(2,4,6,8) and employmenttypeapplication.JobApplicationID=jobapplication.JobApplicationID and experiencetypeapplication.ExperienceTypeID in(1,2,3,4,5,6,7) and experiencetypeapplication.JobApplicationID=jobapplication.JobApplicationID group by jobapplication.JobApplicationID
Explain QueryResult:
table || type || possible_keys || key || key_len || ref || rows || Extra
experiencetypeapplication || index || JobApplicationID || JobApplicationID || 5 || NULL || 69097 || Using where; Using index; Using temporary; Using filesort
jobapplication || eq_ref || PRIMARY || PRIMARY || 4 experiencetypeapplication.JobApplicationID || 1 || Using where
employmenttypeapplication || ref || JobApplicationID || JobApplicationID || 4 jobapplication.JobApplicationID || 3 || Using where; Using index
Thanks,
Mkishor
