List:General Discussion« Previous MessageNext Message »
From:Richard Baskett Date:July 31 2002 9:17am
Subject:Speed issues...
View as plain text  
This is my first post here.. So be nice! ;)

I have a database with a little over 60,000 records and when I do a search
on that db it takes a little over a minute and a half to return results.
This is very unacceptable, but Im just not sure what I can do to remedy
this.  Here is what the query looks like when just using location and
category in the search..

SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS
Comp, Jobs.JobID, Employers.EmpID
FROM Employers
LEFT JOIN Jobs USING (EmpID)
LEFT JOIN JobsLocation USING (JobID)
LEFT JOIN Location USING (LocID)
LEFT JOIN JobsCategory ON Jobs.JobID=JobsCategory.JobID
LEFT JOIN Category USING (CatID)
WHERE Valid = '1' AND JobTitle IS NOT NULL
AND (LocName LIKE 'US-Alabama-Birmingham%')
AND Category.CatID IN ('2')
ORDER BY Loc ASC LIMIT 0,50

Add on the following when using keywords in the search, these are the jobs
to search in since these had the keywords within them:

AND Jobs.JobID IN ('2345','6578')

Like I said it's taking between 45-100 seconds to execute these queries and
that's just not acceptable.  I was told to use another table that would save
the query, save a timestamp, and save the results.  Then whenever that same
query came up just show the stored results.  While this is a possibility, I
see the likelihood of people doing the exact same search as very slim.  So
hopefully there is a better way of doing my query.. Or if there is something
I can tell my host to do with mysql or the hardware that it's on... Im
desperate.. Anything that can speed things up will be appreciated!

Cheers!

Rick

"The intuitive mind is a sacred gift and the rational mind is a faithful
servant.  We have created a society that honors the servant and has
forgotten the gift." - Albert Einstein

Thread
Speed issues...Richard Baskett31 Jul
  • Re: Speed issues...Roger Baklund31 Jul
  • Re: Speed issues...Tod Harter31 Jul
  • Re: Speed issues...Troy Hakala31 Jul
Re: Speed issues...Richard Baskett31 Jul
  • Re: Speed issues...Dan Nelson31 Jul
    • Re: Speed issues...Richard Baskett31 Jul
      • Re: Speed issues...Dan Nelson1 Aug
        • Re: Speed issues...Richard Baskett1 Aug
          • Re: Speed issues...Richard Baskett1 Aug
          • Re: Speed issues...Dan Nelson1 Aug
  • Re: Speed issues...Tod Harter2 Aug