[ACCEPTED]-Doing calculations in MySQL vs PHP-maintainability

I'd play to the strengths of each system.

Aggregating, joining 76 and filtering logic obviously belongs on 75 the data layer. It's faster, not only because 74 most DB engines have 10+ years of optimisation 73 for doing just that, but you minimise the 72 data shifted between your DB and web server.

On 71 the other hand, most DB platforms i've used 70 have very poor functionality for working 69 with individual values. Things likes date 68 formatting and string manipulation just 67 suck in SQL, you're better doing that work 66 in PHP.

Basically, use each system for what 65 it's built to do.

In terms of maintainability, as 64 long as the division between what happens 63 where is clear, separating these to types 62 of logic shouldn't cause much problem and 61 certainly not enough to out way the benefits. In 60 my opinion code clarity and maintainability 59 are more about consistency than about putting 58 all the logic in one place.

Re: specific 57 examples...

1. I know this isn't what you're 56 referring too but dates are almost a special 55 case. You want to make sure that all dates 54 generated by the system are created either 53 on the web server OR the database. Doing 52 otherwise will cause some insidious bugs 51 if the db server and webserver are ever 50 configured for different timezones (i've 49 seen this happen). Imagine, for example, you've 48 got a createdDate column with a default of getDate() that is 47 applied on insert by the DB. If you were to insert 46 a record then, using a date generated in PHP (eg 45 date("Y-m-d", time() - 3600), select records created in the last hour, you 44 might not get what you expect. As for which 43 layer you should do this on, i'd favour 42 the DB for, as in the example, it lets you 41 use column defaults.

2. For most apps i'd do 40 this in PHP. Combining first name and surname 39 sounds simple until you realise you need 38 salutations, titles and middle initials 37 in there sometimes too. Plus you're almost 36 definitely going to end up in a situation 35 where you want a users first name, surname 34 AND a combine salutation + firstname + surname. Concatenating 33 them DB-side means you end up moving more 32 data, although really, it's pretty minor.

3. Depends. As 31 above, if you ever want to use them separately 30 you're better off performance-wise pulling 29 them out separately and concatenating when 28 needed. That said, unless the datasets your 27 dealing with are huge there are probably 26 other factors (like, as you mention, maintainability) that 25 have more bearing.

A few rules of thumb:

• Generating incremental ids should happen in the DB.
• Personally, i like my default applied by the DB.
• When selecting, anything that reduces the number of records should be done by the DB.
• Its usually good to do things that reduce the size of the dataset DB-side (like with the strings example above).
• And as you say; ordering, aggregation, sub-queries, joins, etc. should always be DB-side.
• Also, we haven't talked about them but triggers are usually bad/necessary.

There 24 are a few core trade-offs your facing here 23 and the balance really depends on you application.

Some 22 things should definitely-everytime-always 21 be done in SQL. Excluding some exceptions 20 (like the dates thing) for lot of tasks 19 SQL can be very clunky and can leave you 18 with logic in out of the way places. When 17 searching your codebase for references to 16 a specific column (for example) it is easy 15 to miss those contained in a view or stored 14 procedure.

Performance is always a consideration 13 but, depending on you app and the specific 12 example, maybe not a big one. Your concerns 11 about maintainability and probably very 10 valid and some of the performance benefits 9 i've mentioned are very slight so beware 8 of premature optimisation.

Also, if other 7 systems are accessing the DB directly (eg. for 6 reporting, or imports/exports) you'll benefit 5 from having more logic in the DB. For example, if 4 you want to import users from another datasource 3 directly, something like an email validation 2 function would be reusable is implemented 1 in SQL.

Short answer: it depends. :)

I don't like reinventing the wheel. I also 4 like to use the best tool possible for the 3 task needed to be done, so:

• When I can get the resultset straight from DB without further processing I do it - your case it's a simple query with a simple WHERE clause. Imagine what happens when you have 10 millions users and you get them to PHP, just to need 100 of them - you guessed it - it's very possible for your web server to crash
• When you need to get data from 2 or more tables at once, again, MySQL is much better than PHP
• When you need to count records - the DB is great at it
• I tend to favor application level processing to FK constraints
• Also, I tend to avoid stored procedures, preferring to implement that business logic at application level (unless, of course we are talking about huge data sets).

In conclusion, I 2 would say that your colleague is right in 1 the case presented

If you put half your logic in the database 17 and the other half in the php, then 6 months 16 down the track when you come to make a change 15 it will take you twice as long to figure 14 out what is going on.

Having said that though, your database queries should have just enough logic so that they provide your php with exactly the data it needs. If 13 you are finding yourself looping through 12 thousands of mysql records in your php code, then 11 you are doing something wrong. On the other 10 end of the scale though, if you're running 9 if / else statements in your mysql queries 8 you are also doing something wrong (probably 7 just need to rewrite your query).

I'd steer 6 clear of stored procedures. While they 5 are a great concept in theory you can usually 4 accomplish the same result in the php with 3 a much faster development time and you also 2 have the added benefit of knowing where 1 all the logic is.

MySQL will scale better as result sets increase. Frankly, treating 6 a database as a "dumb data" repository is 5 a waste of resources...

Maintainability tends 4 to be tainted by familiarity. If you're 3 not familiar with PHP, it wouldn't be your 2 initial choice for maintainability -- would 1 it?

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

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

Some basic points:

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

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

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

4. Ordering data 8 should always be done in Mysql

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

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

7. Want to count records, SQL 1 is great.

Answers to each as follows:

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

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

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

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

5. Concatenating 18 a string?

6. Again, PHP for string manipulation.

(thoughts, folks?)

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

Clear 12 examples belonging in the SQL domain:

specific 11 WHERE selections -yes.

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

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

Selecting DISTINCT items -yes.

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