List:General Discussion« Previous MessageNext Message »
From:Richard Baskett Date:July 31 2002 5:41pm
Subject:Re: Speed issues...
View as plain text  
Ok here is the EXPLAIN query:

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