Doing calculations in MySQL vs PHP

user_878354 0
Viewed 29469

Context:

  • We have a PHP/MySQL application.
  • Some portions of the calculations are done in SQL directly. eg: All users created in the last 24 hours would be returned via an SQL query ( NOW() – 1 day)

There's a debate going on between a fellow developer and me where I'm having the opinion that we should:

A. Keep all calculations / code / logic in PHP and treat MySQL as a 'dumb' repository of information

His opinion:

B. Do a mix and match depending on whats easier / faster. http://www.onextrapixel.com/2010/06/23/mysql-has-functions-part-5-php-vs-mysql-performance/

I'm looking at maintainability point-of-view. He's looking at speed (which as the article points out, some operations are faster in MySQL).

@bob-the-destroyer @tekretic @OMG Ponies @mu is too short @Tudor Constantin @tandu @Harley

I agree (and quite obviously) efficient WHERE clauses belong in the SQL level. However, what about examples like:

  1. Calculating a 24 period using NOW() - 1 day in SQL to select all users created in last 24 hours?
  2. Return capitalized first name and last name of all users?
  3. Concatenating a string?
  4. (thoughts, folks?)

Clear examples belonging in the SQL domain:

  1. specific WHERE selections
  2. Nested SQL statements
  3. Ordering / Sorting
  4. Selecting DISTINCT items
  5. Counting rows / items
maintainability mysql php performance
6 Answers

The time taken to fetch the data in SQL is time consuming but once its done calculations are more over same. It won't be much time consuming either way after data is fetched but doing it smartly in the SQL can give better results for large data sets.

If you are fetching data from MYSQL and then doing the calculations in PHP over the fetched data, then its far better to fetch the required result and avoid PHP processing, as it will increase more time.

Some basic points:

  1. Date formatting in MYSQL is strong, most formats are available in Mysql. If you have very specific date format then you can do it PHP.

  2. String manipulation just suck in SQL, better do that work in PHP. If you have not big string manipulation needed to do, then you can do it in Mysql SELECTs.

  3. When selecting, anything that reduces the number of records should be done by the SQL and not PHP

  4. Ordering data should always be done in Mysql

  5. Aggregation should be always done in Mysql because DB engines are specifically designed for this.

  6. Sub-Queries and Joins should always be DB-side. It will reduce your lots of PHP code. When you need to get data from 2 or more tables at once, again, SQL is much better than PHP

  7. Want to count records, SQL is great.

user_10454440

Answers to each as follows:

  1. Calculating a 24 period using NOW() - 1 day in SQL to select all users created in last 24 hours?

  2. Use PHP to create the date and a WHERE clause to seek the data. Date manipulation is much quicker to implement in PHP.

  3. Return capitalized first name and last name of all users?

  4. Select all users in database and then use PHP to capitalise the strings. Again it's much quicker to implement in PHP.

  5. Concatenating a string?

  6. Again, PHP for string manipulation.

(thoughts, folks?)

Use PHP for all data manipulation as it's easier to implement. To be clearer, manipulating a simple $variable in PHP is easier than writing out an entire string manipulation in SQL. Manipulate in PHP and then update database in SQL.

Clear examples belonging in the SQL domain:

specific WHERE selections -yes.

Nested SQL statements -I would reassess you PHP data handling but if you must, ok.

Ordering / Sorting -Ordering is an SQL statement's job for sure but you should only be ordering while on a SELECT statement. Any other ordering such as ordering and UPDATING the database, should be ordered by PHP because again, it's easier to manipulate $vars than it is to write out UPDATE SQL statements.

Selecting DISTINCT items -yes.

Counting rows / items -use: $Number_Of_Results = count($Results); in PHP.

user_128644790
Related