Calculations / Formatting with MySql or PHP ?
Moderator: General Moderators
Calculations / Formatting with MySql or PHP ?
When I first started programming, I kept hearing that when doing queries and needing to do calulcations and formatting the data, you should always let MySQL do the work instead of PHP. Is this true and why or why not?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
From what I understand, complex math is better handled in PHP. Regular math and calculations that can be handled by the DB are better suited for the DB. It balances the timing of the execution of the script and allows the DB to use some resource instead of the code (so your code can be busy doing other things).
mySQL is most certainly faster at msot string formatting and basic math, but there are other things to consider.
For instance, at my company we have 2 services. A real time data feed to several companies (Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome). Also, several websites that are the same back end but are hosted for various companies (generally offering loans in exchange for certain information and permission to sell your information). The database for the real time data feed, is massive. It's comprised of several tables in the hundreds of millions of rows as we're required to keep TONS of legacy data, and we receive about 1.5 million entries into said system every day. The same database server hosts an entirely seperate database process that hosts databases for a couple of the aforementioned websites. If I were to query a 300,000,000 row table, and throw in a bunch of date conversions and string formatting and even the most basic of math, I'd be fired for lagging dozens of websites. Querying those tables is just insanely intensive(They really need a redesign, but that part of the system was here way before i was, and they are happy with it the way it is). Anyway the point of the matter is, there are situations where you'll want PHP doing as much of the work as possible, simply because there are other people using the database server that are, quite frankly, more important than you. However, I doubt you'll find many of those situations on your typical website and in those cases then you'll want mySQL doing it.
For instance, at my company we have 2 services. A real time data feed to several companies (Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome). Also, several websites that are the same back end but are hosted for various companies (generally offering loans in exchange for certain information and permission to sell your information). The database for the real time data feed, is massive. It's comprised of several tables in the hundreds of millions of rows as we're required to keep TONS of legacy data, and we receive about 1.5 million entries into said system every day. The same database server hosts an entirely seperate database process that hosts databases for a couple of the aforementioned websites. If I were to query a 300,000,000 row table, and throw in a bunch of date conversions and string formatting and even the most basic of math, I'd be fired for lagging dozens of websites. Querying those tables is just insanely intensive(They really need a redesign, but that part of the system was here way before i was, and they are happy with it the way it is). Anyway the point of the matter is, there are situations where you'll want PHP doing as much of the work as possible, simply because there are other people using the database server that are, quite frankly, more important than you. However, I doubt you'll find many of those situations on your typical website and in those cases then you'll want mySQL doing it.
lol oddly enough it was one of the most interesting projects I ever worked on. I'd never written anything that just stays running all the time, and sleeps most of the time but as soon as new stuff arrives just does it's thing and sends it where it needs to go, let alone written something like that in PHP. Of course, I thought it was dumb to write it in PHP, but that's what the boss man said, since all of our developers here are PHP and they wanted other people to be able to work with it. Not to mention this project generates well over $3 million in revenue a month, and it's kinda cool know something I wrote makes THAT much money for the company. Now I think I need a raise come to think of itThe Ninja Space Goat wrote:??DrTom wrote:(Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome)you are evil.
Reason I ask is, what if you are using the same query in seperate places for your application. What if you SELECT a date, and in one place you want to format by 01-01-2001? What if in another place you want to format it 01-01-01? How about another, you want do, January 1, 2001?
Also, what if you have another query where you SELECT all the items bought today.
i.e.
What if you want to get the sum of number of items bought that day, would you use a SUM in the MySQL, or do it with PHP? Also, what if the items cost $5 and you want to get the total dollar amount sold that day? Would you then write a seperate query and do the calculation in the query, or would you use the same query and just do it with PHP?
Also, what if you have another query where you SELECT all the items bought today.
i.e.
Code: Select all
SELECT items
FROM items
WHERE date = '2001-01-01'- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Sorry, but don't we have a policy here to what people can say?DrTom wrote:Yes, I wrote the software that sells your info to people who spam your email, call you during dinner time, and send text messages to your phone. Because of me hundreds of thousands of people get annoyed every day, and I think thats awesome
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA