I am working as php developer, and I have an issue with MySQL fulltext searches.
This is my query
select distinct j.jobid,headline,company,country,state,city,location,
date_format(str_to_date(posted_dt, '%m-%d-%Y %H:%i:%s' ), '%M %d, %Y') posted_dt,joblinks
from jobs j
left join job_filters jf on jf.jobid = j.jobid
left join job_emptype_map em on j.jobid = em.jobid
left join job_sub_emptype_map sem on em.jobid = sem.jobid and em.emp_type = sem.emp_type
where status=1
And MATCH (headline,description,pri_skills,company) AGAINST ('java developer' IN natural language MODE) > 1
And MATCH (location,country,state,city,zipcode,other_loc) AGAINST ('california, United States' WITH QUERY EXPANSION ) > 1
order by str_to_date(posted_dt,'%m-%d-%Y %H:%i:%s') DESC limit 0,25
I am getting proper results with above query. But it is taking (Showing rows 0 - 24 (25 total, Query took 50.6951 seconds.)
when I change the keyword "ui developer" instead of "java developer" then I am getting 0 results
if i check keyword "ui developer" with like function then i am getting results.
I am not understanding why match function is working for some keywords only and why it is taking too much of time when my hosting plan is cloud.
I am on cloud server plan now and I have updated my query with like function, now my query is...
select distinct j.jobid,headline,company,country,state,city,location,date_format(str_to_date(posted_dt, '%m-%d-%Y %H:%i:%s' ), '%M %d, %Y') posted_dt,joblinks, (select count(distinct j.jobid) as jobcount from jobs j left join job_filters jf on jf.jobid = j.jobid left join job_emptype_map em on j.jobid = em.jobid left join job_sub_emptype_map sem on em.jobid = sem.jobid and em.emp_type = sem.emp_type where status = 1 and (concat(headline,' ',description,' ',pri_skills,' ',company) like '%java programer%' ) And ( country like '%United States%' ) ) jobcount from jobs j left join job_filters jf on jf.jobid = j.jobid left join job_emptype_map em on j.jobid = em.jobid left join job_sub_emptype_map sem on em.jobid = sem.jobid and em.emp_type = sem.emp_type where status=1 and (concat(headline,' ',description,' ',pri_skills,' ',company) like '%java programer%' )And ( country like '%United States%' ) order by str_to_date(posted_dt,'%m-%d-%Y %H:%i:%s') DESC limit 0,25
i have writen select query between above query for pagination, above query is getting good results but taking too much of time, Pleas Note: my jobs table has around 50,000+ records