Well, I decided to stop being a moron and think inside the box and figured out that it was the total and utter lack of keys and indexes on the newly created MySQL tables which caused the execution time problem. So, the problem turned out to be, "I'm a moron."
Thanks everyone who replied! Here's the final query. It runs in about .3 second: SELECT Dealers.*, ROUND(SQRT(POW(2285-Zips.North,2)+POW(4760-Zips.West,2)),1) AS Distance FROM Dealers INNER JOIN Zips ON Dealers.Zip = Zips.Zip ORDER BY Distance > -----Original Message----- > From: Asendorf, John [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 27, 2002 1:12 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: Trouble converting SQL from Access > > > OK, I've figured out how to fix the SQL (was searching for > "equations" and > "calculations" when I should have been looking for "math" in > the docs!)... > but now it takes EONS longer than the Jet calculations... > > SELECT Dealers.*, > SQRT(POW((2285-Zips.North),2)+POW((4760-Zips.West),2)) AS > Distance > FROM Dealers > INNER JOIN Zips ON Dealers.Zip = Zips.Zip > ORDER BY POW((2285-Zips.North),2)+POW((4760-Zips.West)),2) > > Any suggestions to speed this guy up? There are 42,000 > records in the Zips > table and 4,000 in the Dealers table. The machine has a quad > processor and > runs the same query (below) in a second or two via > access/ASP. With PHP/My > it is taking 99% of the processor and doesn't finish. > > --------------------- > John Asendorf - [EMAIL PROTECTED] > Web Applications Developer > http://www.lcounty.com - NEW FEATURES ADDED DAILY! > Licking County, Ohio, USA > 740-349-3631 > Nullum magnum ingenium sine mixtura dementiae fuit > > > > -----Original Message----- > > From: Asendorf, John [mailto:[EMAIL PROTECTED]] > > Sent: Friday, December 27, 2002 10:03 AM > > To: [EMAIL PROTECTED] > > Subject: Trouble converting SQL from Access > > > > > > I'm attempting to convert an application from ASP/Access to > > PHP/MySQL My > > problem is in the SQL conversion. > > > > The SQL determines distance between two zip codes: > > > > This is the Access original: > > > > SELECT Dealers.*, ((2285-Zips.North)^2+(4760-Zips.West)^2)^.5 > > AS Distance > > FROM Dealers INNER JOIN Zips ON Dealers.Zip = Zips.Zip ORDER BY > > (2285-Zips.North)^2+(4760-Zips.West)^2 > > > > The numbers in the calculations (such as the 2285 and 4760) > > are brought in > > from the Zips table prior to this piece of SQL being sent. > > I've tried a > > number of things (breaking it down into smaller chunks, > changing ON to > > USING, etc.) but don't seem to be able to make the > > calculation work in the > > SQL. > > > > Any help here? > > > > Thanks, John > > > > > > > > > > --------------------- > > John Asendorf - [EMAIL PROTECTED] > > Web Applications Developer > > http://www.lcounty.com - NEW FEATURES ADDED DAILY! > > Licking County, Ohio, USA > > 740-349-3631 > > Nullum magnum ingenium sine mixtura dementiae fuit > > > > > --------------------------------------------------------------------- > > Please check > "http://www.mysql.com/Manual_chapter/manual_toc.html"; before > posting. To request this thread, e-mail > [EMAIL PROTECTED] > > To unsubscribe, send a message to the address shown in the > List-Unsubscribe header of this message. If you cannot see it, > e-mail [EMAIL PROTECTED] instead. > > --------------------------------------------------------------------- > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. --------------------------------------------------------------------- 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
