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
