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. --------------------------------------------------------------------- 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
