Arghhh Can it be done?!?!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Arghhh Can it be done?!?!

Post by Benjamin »

Ok here is the deal...

Code: Select all

ORDER BY POW((69.1*(Longitude-\"$Longitude\")*cos($Latitude/57.3)),\"2\")+POW((69.1*(Latitude-\"$Latitude\")),\"2\") ASC
And yes I used PHP tags on purpose because I don't like GREEN ok.

Anyway, This bit of MySQL up there is costing 3 to 8 seconds per query. I considered feyds recommendation of putting as much of the math as possible into the PHP, but the problem is that deep inside the equation is one of the database variables.

Is it possible to order the results by distance without using this equation in the query? If not, is it possible to speed this up?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

Get a quad-xeon 3.3 ghz database server, should speed that query up quite a bit :wink:
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I don't take the easy way out... :D
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

as I said before, you can solve out and simplify things.

Code: Select all

$Longitude *= -1;
$cos = cos($Latitude / 57.3);
$n = 69.1;
$a = $n * $cos; // to multiply by `Longitude`
$b = $a * $Longitude; // to add to `Longitude` (post $a mul)
$c = $Latitude * -$n; // to add to `Latitude` (post $n mul)
$foo = "ORDER BY
(
  (`Longitude` * {$a} + {$b}) * (`Longitude` * {$a} + {$b}) + 
  (`Latitude` * {$n} + {$c}) * (`Latitude` * {$n} + {$c})
)";
That is so untested.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Arghhh Can it be done?!?!

Post by RobertGonzalez »

agtlewis wrote:Is it possible to order the results by distance without using this equation in the query? If not, is it possible to speed this up?
No, distance will always be calculated. It will either be calculated in the query or code-side, but it is always calculated.

The general process is to identify a zipcode, locate the lat and lon associated with that zipcode, then, using the radius, draw a circle around the lat and lon point to determine the set of points within the circle. That is fairly easy to for the database. However, from one point to another, you need to identify each lat/lon pair for each zipcode within the circle THEN run that crazy equation to calculate the distance.

I have tried to so what you are doing both ways. I have found, through many a late night and early morning headache, that the most efficient way to calculate the distance and sort by it is with your code, not the database. To do this, grab all the records in the database that fall within the circle. Use PHP to calculate the distance within the result array, then when it comes times to display the results, sort the array on the created 'distance' key. Experience (at least my experience) has shown that this is the fastest and most efficient way to run the calculations.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Arghhh Can it be done?!?!

Post by Benjamin »

Everah wrote:No, distance will always be calculated. It will either be calculated in the query or code-side, but it is always calculated.
Everah, I realize that the distance has to be calculated, I am just trying to pull it out of the query. No sense calculating it for every record when you can do it in PHP once right? Just trying to save a server from an early death.

Vision....

Code: Select all

$IamGoingToKillTheServer = '1903983.43332';
$FeydsSolution = '222';

$EquationInTheDB = POW(rand(rand(2,99),COS(CEIL(rand(3,9292)))));
$IamRunningFine = $IamGoingToKillTheServer / COS(POW($FeydsSolution / 10));

if ($EquationInTheDB < $IamRunningFine) {
  echo "feyds the winner";
} else {
  echo "Get a quad-xeon 3.3 ghz database server, should speed that query up quite a bit";
}
Anyone want to run that and see what happens lol?

I'll try that code out later. (Feyds Example) For now I need to get some sleep before I accidently write a PHP AI script.
User avatar
php3ch0
Forum Contributor
Posts: 212
Joined: Sun Nov 13, 2005 7:35 am
Location: Folkestone, Kent, UK

Post by php3ch0 »

-
Last edited by php3ch0 on Mon Mar 27, 2006 7:20 am, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

feyd wrote:

Code: Select all

$Longitude *= -1;
$cos = cos($Latitude / 57.3);
$n = 69.1;
$a = $n * $cos; // to multiply by `Longitude`
$b = $a * $Longitude; // to add to `Longitude` (post $a mul)
$c = $Latitude * -$n; // to add to `Latitude` (post $n mul)
$foo = "ORDER BY
(
  (`Longitude` * {$a} + {$b}) * (`Longitude` * {$a} + {$b}) + 
  (`Latitude` * {$n} + {$c}) * (`Latitude` * {$n} + {$c})
)";
That is so untested.
Feyd, thank you for the help. I tested this code snippet and it does work, however there is no noticable speed difference. I was able to increase the speed of the query by removing the joins by a factor of 3. I'll just have to merge some of the tables.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Just some FYI, the problem wasn't with the query itself as much as it was with the MySQL configuration. I reconfigured MySQL memory usage and can now execute the query in 0.00528 seconds with over 350k records. I also ordered the table by Latitude.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post by Ree »

What exactly did you configure?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I added the following to /etc/my.cnf

Code: Select all

[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
Then restarted MySQL.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

It may also speed things up by throwing an index on the Longitude and Latitude columns
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Thanks pickle I did that as well.
Post Reply