List:General Discussion« Previous MessageNext Message »
From:Tod Harter Date:August 2 2002 6:38pm
Subject:Re: Speed issues...
View as plain text  
On Wednesday 31 July 2002 01:41 pm, Richard Baskett wrote:
> Ok here is the EXPLAIN query:

OK, 1st thing this is telling you is that you aren't using any index on your 
master table, Employers. Probably this just means there isn't any field in 
that table that is part of the search (I can't be 100% sure of that without 
knowing the schema). If one of the columns in Employers IS part of the search 
criteria, then by all means index it, that will cut down the 46,175 rows you 
need to select to start with.

See what's happening is that first you get 46175 rows from Employers (which 
is the entire table), then you attempt to join it to Jobs, which matches 3 
rows, meaning 46,175 TIMES 3 comparisons to do the join. Then the result of 
THAT is being compared with JobsLocation, which is doing another bunch of 
comparisons (we don't know exactly how many, but at least 46,175 TIMES 
581....). etc.

It looks like you have indexes on everything you can, but as someone else 
suggested it may be beneficial to get rid of 'left join' so MySQL can reorder 
the joins for maximum efficiency. I THINK if it can join to JobsCategory 
first it will cut down the work done substantially. 

Check out the MySQL manual section on EXPLAIN as well, there are probably 
some subtle things I've missed. Sometimes just tinkering with your query can 
help. I would try removing a few joins just to see which tables are slowing 
you down the most. You might be able to denormalize a bit based on that.
>
> table, type, possible_keys, key, key_len, ref, rows, Extra
> Employers, ALL, NULL, NULL, NULL, NULL, 46175, where used; Using temporary;
> Using filesort
> Jobs, ref, EmpID, EmpID, 4, Employers.EmpID, 3, where used
> JobsLocation, ref, PRIMARY, PRIMARY, 4, Jobs.JobID, 581, Using index
> Location, eq_ref, LocID, LocID, 2, JobsLocation.LocID, 1, where used
> JobsCategory, ref, PRIMARY, PRIMARY, 4, Jobs.JobID, 581, Using index;
> Distinct
> Category, eq_ref, PRIMARY,CatID, PRIMARY, 1, JobsCategory.CatID, 1, where
> used; Using index; Distinct
>
> Hopefully you can make out what it says in all of that jumble! :)
>
> Thanks so much for everyone's help!
>
> I am using PHP by the way.. And the reason those things that do not have to
> be quoted are.. Is.. Well.. Just because I quote everything, less likely to
> run into errors, but if it would speed things up.. By all means I will take
> them out! yes?
>
> Cheers!
>
> Rick
>
> "When the solution is simple, God is answering." - Albert Einstein
>
> > From: Tod Harter <tharter@stripped>
> > Organization: Giant Electronic Brain
> > Date: Wed, 31 Jul 2002 09:57:20 -0400
> > To: Richard Baskett <rick@stripped>, MySQL
> <mysql@stripped>
> > Subject: Re: Speed issues...
> >
> > 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
> >
> > ---------------------------------------------------------------------
> > 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-thread116003@stripped>
> > To unsubscribe, e-mail
> > <mysql-unsubscribe-rick=baskettcase.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