List:General Discussion« Previous MessageNext Message »
From:Troy Hakala Date:July 31 2002 3:21pm
Subject:Re: Speed issues...
View as plain text  
are there indexes on the right fields? do a "describe" on the query and 
let us know what you get.

On Wednesday, July 31, 2002, at 02:17 AM, Richard Baskett wrote:

> 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
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread115981@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-
> troyhakala=attbi.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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