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.
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?
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.
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.
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…
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.