Doing more in mysql over php - in general a bad idea or good one?

[ShinVe]

MySQL offers some ways to perform things that you might put in your application logic. For example, say you want to find the oldest people in a database with an age column.

SELECT name, age FROM persons WHERE age = (SELECT MAX(age) FROM persons)

This would select only the rows that have the highest integer in the age column (so it would select one or more people).

But you could also just do:

"SELECT name, age FROM persons"

and then use the results of this query to do some php to find the oldest people.

In general, which is better for speed and performance? I generally try to minimize using my database usage for doing logic, but I don't know if this is a good plan or where to draw the line. It would be helpful to have a good rule of thumb to judge whether I should use the database to do a calculation or organization over php and vice versa.

[guelphdad]

Do the work in your database, use your front end application (PHP or whatever) to display the results in a stylized format for web or otherwise. The work belongs in the database.

[ShinVe]

Although this was a simple example, sometimes a query might take a long time, relatively speaking, and running that many times simultaneously sounds like it might be bad for a database. When should I opt for doing things in php rather than the database?

[r937]

When should I opt for doing things in php rather than the database?

it depends on the situation

[ShinVe]

Just for understandings sake, could you give me an example of something I could do in mysql that I should do in PHP? (I really don't have any guidelines to go by. )

[r937]

example 1

a printout that is supposed to look like this --

Hardware
    motherboards
    sound boards
    tuners
Software
    oh esses
    dee bees
    solitaire

example 2

counting as well as displaying details --

SELECT COUNT(*) FROM daTable;
SELECT * FROM daTable;

[Doug G]

Another consideration is whether or not you expect your application will need to support different backend database systems. If you put a lot of logic in the dbms using db procedures, etc, it can be difficult or impossible to port to a different dbms.

[r937]

that sounds like the type of fear, uncertainty and doubt that i used to hear in the days of the database wars between the likes of oracle, ibm, and microsoft

[Doug G]

that sounds like the type of fear, uncertainty and doubt that i used to hear in the days of the database wars between the likes of oracle, ibm, and microsoft

No FUD, just a comment based on experience from working on a multi-db web application that supported 3 different backend dbms. If you are working with such an app you have to decide on just what functionality offered by a dbms you choose to take advantage of, and what functionality you end up implementing within your application code.

[r937]

you are right, and i was just teasing you

but converting from one database's stored procs to another's isn't hard, and you should put whatever naturally belongs in a stored proc into the stored proc and not in application code just because you might want to change databases some day...

[ShinVe]

example 1

a printout that is supposed to look like this --

Hardware
    motherboards
    sound boards
    tuners
Software
    oh esses
    dee bees
    solitaire

example 2

counting as well as displaying details --

SELECT COUNT(*) FROM daTable;
SELECT * FROM daTable;

Thanks. I didn't understand your second example though. How can you do a SELECT * FROM tablem or do a count of rows in PHP only? That seems like you actually have to rely on the database to get the information from these selects.

[r937]

I didn't understand your second example though.

if you're going to retrieve all the rows, there's no point in also asking the database to count them -- just use the php function mysql_num_rows

[ShinVe]

I got it. So if a php-specific function exists to get some (aggregate or meta) information from the db, use that.