Page 1 of 1
Is there a way to reduce loading time by just editing query?
Posted: Tue Jan 31, 2006 12:55 am
by x
Well here is a code that takes 25seconds to load and is reported to take 11 seconds to load on the phpmyadmin
Code: Select all
SELECT
activeResume.resumeId, Resume.datePosted, Resume.resumeTitle, Resume.cityLocation, Resume.stateLocation, Resume.userId, JobApplicant.name, JobApplicant.currentJobTitle, JobApplicant.salaryLow, JobApplicant.salaryHigh, JobApplicant.city, JobApplicant.state
FROM
activeResume
LEFT JOIN
Resume on activeResume.resumeId=Resume.resumeId
LEFT JOIN JobApplicant ON Resume.userId = JobApplicant.userId
WHERE activeResume.resumeId > 0 and
( ( match(fulltextresume,resumeText,resumeTitle,objective,jobTitle1,jobTitle2,jobTitle3) against ('engineer' in boolean mode) ) )
ORDER BY
Resume.datePosted DESC LIMIT 0 , 30
well this may seem too boring to you but i just want to know if its possible to reduce time of execution by just changing the order of joins or whatever in a query?
that code also take only .5 seconds when i removed the order
Posted: Tue Jan 31, 2006 1:37 am
by josh
Do you have an index on the field you were ordering by? I bet not
Posted: Tue Jan 31, 2006 2:06 am
by x
yup,
INDEXED
userId
searchLocationId
jobCategoryId
datePosted
resumeTitle
resumeText
cityLocation
stateLocation
and
FULLTEXT
fulltextresume
resumeTitle
objective
jobTitle1
jobTitle2
jobTitle3
resumeTitle
there are 30000 entries, and what im thinking right now is probably make a new database with all the columns of this search just for the search engine to work faster since they will all be in a single table, ill just make an updater that should add new entries each day
but please tell me if you know a better way
Posted: Tue Jan 31, 2006 3:34 am
by AKA Panama Jack
I think you will find the problem is the fulltext indexing search on all of those fields at the same time is causing the slowdown. Fulltext indexing is faster than any other method when dealing with text fields but the problem is in how much content in each of the text fields it needs to search through. When performing a match as you are it is having to scan the content of every one of those indicated fields for every record until it hits the last record in the table or finds 30 matches. Having large text fields in this case is going to really slow down that query. The more records you add the longer that query is going to take and it won't just double. If 30k records takes 25 seconds now then 60k records will probably take 70-80 seconds, if not more. And the more data stored in each text field the longer that search will take.
You would be better off breaking down the table into different tables for different areas of a resume' and have targeted searches for those areas.
As it is right now your query is just going to take longer and longer as you gain more entries.
Posted: Tue Jan 31, 2006 5:25 am
by raghavan20
I am wondering is it possible to run two queries simultaneously. What I was thinking is, if you can break up this query and peform search on individual large fields separately as separate queries......may be I am wrong in the way I am thinking...
Posted: Tue Jan 31, 2006 8:05 am
by CoderGoblin
Not sure if this will work but something like
Code: Select all
SELECT * FROM (SELECT activeResume.resumeId FROM activeResume WHERE activeResume.resumeId > 0) as table1
LEFT JOIN (SELECT date_posted,resumeTitle,cityLocation,stateLocation,userId FROM Resume) as table2 on table1.resumeId=resumeId
LEFT JOIN (SELECT * FROM JobApplicant) as table3 on table2.userId=userId
WHERE ( ( match(fulltextresume,resumeText,resumeTitle,objective,jobTitle1,jobTitle2,jobTitle3) against ('engineer' in boolean mode) ) )
ORDER BY Resume.datePosted DESC LIMIT 0 , 30
may work. In this case you are only getting the information you require to be joined. May need to tweak the SQL to work as I always have problems thinking about the correct SQL with joins like this when column name are the same in multiple tables.
Posted: Tue Jan 31, 2006 8:45 am
by sheila
This is interesting
CodeGlobin wrote:that code also take only .5 seconds when i removed the order
jshpro2 wrote:Do you have an index on the field you were ordering by?
CodeGlobin wrote:yup,
INDEXED
userId
searchLocationId
jobCategoryId
datePosted
resumeTitle
resumeText
cityLocation
stateLocation
From this it would seem that the fulltext search is not the problem.
Could the index be corrupt?
Would it be faster to remove the ORDER BY and let PHP sort on datePosted?
Posted: Tue Jan 31, 2006 11:16 am
by Weirdan
Is resumeId indexed? Most of the time JOIN performance could be improved by using index on the fields used in join condition.
Posted: Tue Jan 31, 2006 11:58 am
by josh
I recommend typing "Explain ... your query here..." into the mysql client, it should output information about the indexes it's using, the rows examined, etc... Can you post your ouput here? There's various commands to tell mysql it's not optimizing your query one of which is use_index() or something along those lines, you should be able to find it in the docs at
http://www.mysql.com, also read the section on how mysql optimizes indexes, etc... (This is only relevant material if mysql was not choosing the right index, if it was we have to look further)