List:General Discussion« Previous MessageNext Message »
From:Tod Harter Date:July 31 2002 1:57pm
Subject:Re: Speed issues...
View as plain text  
On Wednesday 31 July 2002 05:17 am, Richard Baskett wrote:
> This is my first post here.. So be nice! ;)

OK, no flames ;o).

I'd want a bit more information in order to really diagnose this. The first 
thing I would tell you to do is to EXPLAIN this query. To do that just tack 
the keyword EXPLAIN onto the beginning of it, this will return you an 
explanation of what MySQL's query optimizer has decided to do with the query. 
Most likely one or more of your tables are being joined on columns with no 
indexes. Generally all the columns mentioned in USING should be indexed. You 
might also experiment with indexing of the LocName and JobTitle columns.

Beyond that there are many possibilities. Your table structure is obviously 
fairly elaborate, and I suspect fully normalized. You might be forced to 
denormalize it some, in other words maybe the stuff in tables like "Location" 
will just have to be merged into the parent table even though it means 
possibly some duplication of data (but I suspect that at the level of 
actually managing your data you probably duplicate it in the child table 
already anyhow...).  Denormalization can reduce the numbers of joins enough 
to possibly get you a speed improvement.

BTW, why is the 1 in ... VALID = '1' ... quoted? If its an integer then get 
rid of the quotes, same for CatID IN ('2'), your just forcing the database 
engine to do conversions. If, as I suspect, you are using Perl DBI then try 
using bind parameters and explicitly setting the SQL_TYPE. Perl DBI is pretty 
dumb about figuring out what data types to use.

>
> 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-tharter=aptusventures.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