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 <[EMAIL PROTECTED]>
> Organization: Giant Electronic Brain
> Date: Wed, 31 Jul 2002 09:57:20 -0400
> To: Richard Baskett <[EMAIL PROTECTED]>, MySQL <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
>> <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
