Query Timing Out

I've got this SQL statement to handle a users search request.

<cfquery datasource="#datasource#" name="get_job_postings">
SELECT t1.JobPostId, t1.Actionlkp, t1.Date,
tbl_job_posting . * ,
tbl_job_post_requirements. * ,
tbl_employer_info . * ,
tblkp_employment_type . * ,
tblkp_education_exp_lvl . * ,
tblkp_salary_range . * ,
tblkp_employment_exp_lvl . * ,
tbl_job_post_location . * ,
tbl_job_post_job_categories .JobCat,
tblkp_countries . * ,
tblkp_prefectures. *,
tblkp_cities.ID, tblkp_cities.FullNameC

FROM `tbl_job_post_history` AS t1

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tbl_job_post_location`
ON tbl_job_post_location.JobPostID = t1.JobPostID

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tbl_job_post_requirements`
ON tbl_job_post_requirements.JobPostID = t1.JobPostID

<!----------------------------------------------------------------------------------------------------------------------------->
LEFT JOIN `tbl_job_post_job_categories`
ON tbl_job_post_job_categories.JobPostID = t1.JobPostID

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_countries`
ON tbl_job_post_location.CC1 = tblkp_countries.CC1

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_prefectures`
ON tbl_job_post_location.CC1 = tblkp_prefectures.CC1
AND tbl_job_post_location.PrefectureID = tblkp_prefectures.ADM1

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_cities`
ON tbl_job_post_location.CityID = tblkp_cities.ID

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tbl_job_posting`
ON tbl_job_posting.JobPostID = t1.JobPostID

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tbl_employer_info`
ON tbl_employer_info.UserID = tbl_job_posting.UserID

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_employment_type`
ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_education_exp_lvl`
ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_salary_range`
ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp

<!----------------------------------------------------------------------------------------------------------------------------->
JOIN `tblkp_employment_exp_lvl`
ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType

<!----------------------------------------------------------------------------------------------------------------------------->
WHERE t1.JobPostId NOT
IN (

SELECT DISTINCT (
JobPostId
)
FROM tbl_job_post_history
WHERE Actionlkp =4
AND date = (

SELECT MAX( date )
FROM tbl_job_post_history
WHERE JobPostId = t1.JobPostId
)
AND JobPostID = t1.JobPostID
)
AND HistID = (

SELECT MAX( HistID )
FROM tbl_job_post_history
WHERE JobPostID = t1.JobPostId
AND Actionlkp =1
)

<!----------------------------------------------------------------------------------------------------------------------------->

<!--- if user selects a country to search !--->
<cfif form.country neq "" AND form.prefecture eq "" AND form.city eq "">
AND tbl_job_post_location.CC1 = '#form.country#'

<!--- if user selects a country and a prefecture to search !--->
<cfelseif form.country neq "" AND form.prefecture neq "" AND form.city eq "">
AND tbl_job_post_location.CC1 = '#form.country#'
AND tbl_job_post_location.PrefectureID = '#form.prefecture#'

<!--- if user selects a country a prefecture and a city to search !--->
<cfelseif form.country neq "" AND form.prefecture neq "" AND form.city neq "">
AND tbl_job_post_location.CC1 = '#form.country#'
AND tbl_job_post_location.PrefectureID = '#form.prefecture#'
AND tbl_job_post_location.CityID = '#form.city#'
</cfif>

<!--- if user selects a job category to search !--->
<cfif form.cat neq 0>
AND tbl_job_post_job_categories.JobCat = '#form.cat#'
</cfif>

<!--- if user enters a keyword to search !--->
<cfif form.keyword neq "">
AND tbl_job_posting.JobTitle LIKE '%#form.Keyword#%'
OR tbl_job_posting.JobDescription LIKE '%#form.Keyword#%'
OR tbl_job_post_requirements.JobRequirements LIKE '%#form.Keyword#%'
</cfif>

<!----------------------------------------------------------------------------------------------------------------------------->
<cfif srt eq "d">
ORDER BY Date DESC
<cfelseif srt eq "c">
ORDER BY tbl_employer_info.CompanyName
</cfif>
</cfquery>

For some reason after I added the form.field search features the query times out... Is there a better way to do this?

* Note: even if I do not enter a keyword and the form.keyword = "". The query still times outs.

 

Look at indexes. You have a very big query and you probably you dont have index for everything... to see more abut your query try mysql "explain" command to see where you need indexes.

Regards
Adrian+

 

I found the trouble... When I Join the tlbkp tables for the location... Country/State/City the query has to look over 3 million records to find the location for each job posting... I do have indexes in those tables...