Page 1 of 1

Arghhh Can it be done?!?!

Posted: Thu Mar 23, 2006 9:27 pm
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?

Posted: Thu Mar 23, 2006 9:49 pm
by Todd_Z
Get a quad-xeon 3.3 ghz database server, should speed that query up quite a bit :wink:

Posted: Fri Mar 24, 2006 2:33 am
by Benjamin
I don't take the easy way out... :D

Posted: Fri Mar 24, 2006 2:54 am
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.

Re: Arghhh Can it be done?!?!

Posted: Fri Mar 24, 2006 2:55 am
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.

Re: Arghhh Can it be done?!?!

Posted: Fri Mar 24, 2006 3:27 am
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.

Posted: Fri Mar 24, 2006 5:48 am
by php3ch0
-

Posted: Mon Mar 27, 2006 7:10 am
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.

Posted: Mon Mar 27, 2006 12:11 pm
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.

Posted: Mon Mar 27, 2006 12:14 pm
by Ree
What exactly did you configure?

Posted: Mon Mar 27, 2006 12:19 pm
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.

Posted: Mon Mar 27, 2006 1:10 pm
by pickle
It may also speed things up by throwing an index on the Longitude and Latitude columns

Posted: Mon Mar 27, 2006 1:11 pm
by Benjamin
Thanks pickle I did that as well.