Which is faster: Math in PHP or Math in SQL?

[pthurmond]

I am trying to clean up a complex SQL query and I am trying to see if the math in the query is faster or slower then doing the math in PHP and then putting the result in the query.

 

Does anyone know the answer to this? Thanks for your time!

 

-Patrick

[Pikachu2000]

Every test result I've seen shows math operations in MySQL to be substantially faster than in PHP.

[roopurt18]

Think carefully about whether it really makes a difference.

 

Try not to micro-optimize to the point that your code is difficult to read and maintain.

[PFMaBiSmAd]

Ummm. What math?

 

As always, in programming, what you are actually doing determines the best way of doing it.

[pthurmond]

In this particular case I am searching for nearby cities in a zip code table. I am just taking old code that someone else wrote a long long time ago and trying to improve its performance. I have since realized that a good portion of the speed issues were coming from a lack of proper indexing on some joined tables.

 

Also when I first got to this code the origin var (that you will see in the code below) was actually full sub-queries that returned the same information each time, just varying either the longitude or latitude field. So I pulled the sub-queries out and made a single call to the DB before this call that gathered those two data points then output them to this query string.

 

(3959 * ATAN(SQRT(1 - SQRT(sin(Latitude / 57.29577951) * sin(({$Origin['Latitude']}) / 57.29577951) + cos(Latitude / 57.29577951) * cos(({$Origin['Latitude']}) / 57.29577951) * COS(ABS(({$Origin['Longitude']}) - Longitude ) / 57.29577951))) / (sin(Latitude / 57.29577951) * sin(({$Origin['Latitude']}) / 57.29577951) + cos(Latitude / 57.29577951) * cos(({$Origin['Latitude']}) / 57.29577951) * COS(ABS(({$Origin['Longitude']}) - Longitude ) / 57.29577951))) ) < 50

 

The $Origin variable replaced sub-queries such as this:

SELECT Longitude FROM zip_code_table WHERE City = 'LUBBOCK' AND State = 'TX' LIMIT 1

 

Removing the sub-queries and replacing them with the actual values seems to have helped some. However, the biggest improves seems to have come from improved indexing.

 

None-the-less my original question still stands, more out of curiosity at this point. Thank you Pikachu2000 for your comment. I am curious if complex math such as what is used above would be any different (PHP or SQL is faster?) then your basic math equations.

 

Also if someone knows a more efficient formula for finding the distance between two points while searching a database then I would welcome the knowledge.

 

Thanks,

Patrick

[kickstart]

Hi

 

Another consideration is what the result is used for. For example there is no point calculating something 100 times in php if doing the calculation in MySQL would exclude the processing of 99% of those records.

 

In you distance calculation you can probably improve efficiency by taking out the bits that resolve to a constant for that query. For example cos(({$Origin['Latitude']}) / 57.29577951) will have the same value for every calculation that it is a part of for any particular origin.

 

All the best

 

Keith

[fenway]

In general, as long as the math isn't used to search the database, you're ok.

[chintansshah]

Mysql is faster then PHP,

 

Because, during the query excution you can check and validate in query itself.

 

your execution time gets reduced.

 

[fenway]

Mysql is faster then PHP,

 

Because, during the query excution you can check and validate in query itself.

 

your execution time gets reduced.

 

Huh?