Math using MySQL or PHP

eagle12 · 06-16-2009, 03:35 PM

I was wondering if anyone knows is it better to perform math functions in PHP or using SQL? Please include reasons.

mholt · 06-16-2009, 06:08 PM

Inline SQL math operations are very speedy.

Depends if you're doing math directly related to the database or if you're compiling the information first and then doing math.

AdamWestdomains · 06-17-2009, 07:48 PM

This is a good question and it would depend on a few considerations,
For most general use, the times would be negligible. SQL code would be faster when math operations have to be done repeatedly to large datasets, and for processing data and adding it back into the database. Part of this involves the fact that large datasets dont need to be transferred to the application/application memory. MySQL i optimized to process data on the db like this, other db's have procedures, etc.
????: NamePros.com http://www.namepros.com/programming/590430-math-using-mysql-or-php.html
Operations that involve storing the results for further processing, and that need to be really dynamic would be best in PHP code, things such as integrals, fatorials, amortization tables etc.
The caveat to SQL code is that its sometimes more complex than just using PHP, and unless you're beyond needing to process tens of thousands of rows, you probably wont see any speed gains.
+1 for PHP

eagle12 · 06-18-2009, 05:52 AM

Thanks for the responses. Here is the SQL I am working with:

Code:

select country.name, IFNULL(unverified,0) as unverified, IFNULL(verified,0) as verified, IFNULL(format(verified/(IFNULL(unverified,0)+verified)*100,2),'N/A') as average, IFNULL(verified + IFNULL(unverified,0),'N/A') as total from country left join  
(select count(*) as verified,country.countryID from school 
left join city using(cityID)
left join province using(provinceID)
left join country using(countryID)
where status = 1
group by country.name
order by country.name) as t1 using(countryID)
left join (select count(*) as unverified,country.countryID from school 
left join city using(cityID)
left join province using(provinceID)
left join country using(countryID)
where status = 0
group by country.name
order by country.name) as t2 using(countryID)

CrackFeed.Com · 06-18-2009, 01:17 PM

I wouldn't run math in sql, it is speedy, but it taxes your resources as well.

sdsinc · 06-18-2009, 01:35 PM

IMO the performance hit will be the multiple joins. I hope you have good indexes.
Perhaps the whole statement could be optimized.

CrackFeed.Com · 06-18-2009, 02:42 PM

I would try to combine tables if possible so you don't have to use joins. I try to avoid them at all costs.

06-16-2009, 03:35 PM	THREAD STARTER #1 (permalink)
eagle12 NamePros Member Join Date: Nov 2003 Location: Ontario, Canada Posts: 127	Math using MySQL or PHP I was wondering if anyone knows is it better to perform math functions in PHP or using SQL? Please include reasons.

06-18-2009, 01:35 PM	#6 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,558	IMO the performance hit will be the multiple joins. I hope you have good indexes. Perhaps the whole statement could be optimized. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
HOWTO: Install the Apache Web Server, Perl, PHP, and MySQL on Windows	deadserious	Webmaster Tutorials	96	05-27-2007 02:24 PM
Great Scripts for Sale With Resale Rights!	Zeeble	Scripts For Sale	20	01-04-2006 02:39 AM

06-16-2009, 06:08 PM	#2 (permalink)
mholt DNOA Member Join Date: May 2004 Posts: 5,040	Inline SQL math operations are very speedy. Depends if you're doing math directly related to the database or if you're compiling the information first and then doing math.

06-17-2009, 07:48 PM	#3 (permalink)
AdamWestdomains Account Suspended Join Date: May 2007 Posts: 163	This is a good question and it would depend on a few considerations, For most general use, the times would be negligible. SQL code would be faster when math operations have to be done repeatedly to large datasets, and for processing data and adding it back into the database. Part of this involves the fact that large datasets dont need to be transferred to the application/application memory. MySQL i optimized to process data on the db like this, other db's have procedures, etc. ????: NamePros.com http://www.namepros.com/programming/590430-math-using-mysql-or-php.html Operations that involve storing the results for further processing, and that need to be really dynamic would be best in PHP code, things such as integrals, fatorials, amortization tables etc. The caveat to SQL code is that its sometimes more complex than just using PHP, and unless you're beyond needing to process tens of thousands of rows, you probably wont see any speed gains. +1 for PHP

06-18-2009, 01:17 PM	#5 (permalink)
CrackFeed.Com Account Closed Join Date: Dec 2008 Location: Boston, Ma Posts: 650 Follow @CrackFeed	I wouldn't run math in sql, it is speedy, but it taxes your resources as well.

06-18-2009, 02:42 PM	#7 (permalink)
CrackFeed.Com Account Closed Join Date: Dec 2008 Location: Boston, Ma Posts: 650 Follow @CrackFeed	I would try to combine tables if possible so you don't have to use joins. I try to avoid them at all costs.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)