I've been doing a lot of calculating stuff nowadays. Usually I prefer to do these calculations in PHP rather than MySQL though I know PHP is not good at this. I thought MySQL may be worse. But I found some performance problem: some pages were loaded so slowly that 30 seconds' time limit is not enough for them! So I wonder where is the better place to do the calculations, and any principles for that? Suggestions would be appreciated.
+1
A:
i would say do calculations in languages that were created for that, like c++. But if you choose between mysql and php, php is better.
Andrey
2010-04-12 16:31:32
Can you elaborate as to what makes it better?
Anthony Forloney
2010-04-12 16:32:07
it is better because mysql was created for data manipulations, and therefore it is optimized for it. language engine is not that good as php's one, because php was done exactly for scripting.
Andrey
2010-04-12 16:36:09
It depends on what kind of calculations. If it's CPU-intensive or arbitrary-precision math, you're better off using C/C++ with libgmp or maybe Python. If it's simple aggregation, do what you can in MySQL without making the MySQL code ridiculously complex, and do the rest in PHP.
Joey Adams
2010-04-13 03:34:00
+19
A:
Anything that can be done using a RDBMS (GROUPING, SUMMING, AVG) where the data can be filtered on the server side, should be done in the RDBMS.
If the calculation would be better suited in PHP then fine, go with that, but otherwise don't try to do in PHP what a RDBMS was made for. YOU WILL LOSE.
astander
2010-04-12 16:33:31
but don't forget visa verse rule: if you do a lot of calculations on one piece of data then it is not for db
Andrey
2010-04-12 16:52:17
I'd upvote this more if I could. OP-my company's product frequently summarises tens of millions of rows into 1 000 or fewer. Their workstations are usually stock-average systems; their database servers are 8- or 16-core beasts with tens of gigs of RAM. Why would we want to run all that data over the wire and crunch it on a memory-limited 1- or 2-processor machine? Do the work as close to the data as you can.
DaveE
2010-04-12 16:53:04
A:
Use PHP, don't lag up your MySQL doing endless calculations. If your talking about things like sorting its OK to use MySQL for stuff like that, SUM, AVG but don't overdo it.
AjayP
2010-04-12 16:33:51
actually, MySQL native functions are very fast and it's often more efficient to use them rather than reprocess "raw" data in PHP. A very common example of this is Date arithmetic. On the other hand, sorting, especially if indices are unavailable, can be prohibitively slow and may, in some cases, be better done in PHP.
dnagirl
2010-04-12 16:42:09
A:
cmptrgeekken is right we would need some more information. BUT if you are needing to do calculations that pertain to database queries or doing operations on them, comparisons certian fields from the database, make the database do it. Doing special queries in SQL is cheaper(as far as time is concerned and it is optimized for that :) ) But both PHP and MySQL are both server side it won't really matter where you are doing the calculations. But like I said before if they are operations on with database information, make a more complicated SQL query and use that.
Shadow
2010-04-12 16:34:31
A:
Native MySQL functions are very quick. So do what makes sense in your queries.
If you have multiples servers (ie, a web server and a DB server), note DB servers are much more expensive then web servers, so if you have a lot of traffic or a very busy DB server do not do the 'extras' that can be handled just as easily/efficiently on a web server machine to help prevent slowdowns.
RDL
2010-04-12 20:26:01
+2
A:
I would recommend doing any row level calculations using the RDBMS.
Not only are you going to benefit from better performance but it also makes your applications more portable if you need to switch to another scripting language, let's say PHP to Python, because you've already sorted, filtered and processed the data using your RBDMS.
It also helps separate your application logic, it has helped me keep my controllers cleaner and neater when working in an MVC environment.
The website-lab
2010-04-12 21:46:55
+1
A:
Just keep track of where your bottlenecks are. If your table gets locked up because you're trying to run some calculations, everyone else is in queue waiting to read/write the data in the selected tables and the queue will continue to grow.
MySQL is typically faster at processing your commands, but PHP should be able to handle simple problems without too much of a fuss. Of course, that does not mean you should be pinging your database multiple times for the same calculation over and over.
You might be better off caching your results if you can and have a cron job updating it once a day/hour (please don't do it every minute, your hosting provider will probably hate you).
Duniyadnd
2010-04-13 03:27:42
+1
A:
Do as much filtering and merging as possible to bring the minimum amount of data into php. Once you have that minimum data set, then it depends on what you are doing, server load, and perhaps other factors.
If you can do something equally well in either, and the sql is not overly complex to write (and maintain) then do that. For simple math, sql is usually a good bet. For string manipulations where the strings will end up about the same length or grow, php is probably a good bet.
The most important thing is to request as little data as possible. The manipulation of the data, at least what sql can do, is secondary to retrieving and transferring the data.
drawnonward
2010-04-13 04:31:31