SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ShinVe View Post
    When should I opt for doing things in php rather than the database?
    it depends on the situation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. )

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    example 1

    a printout that is supposed to look like this --
    Code:
    Hardware
        motherboards
        sound boards
        tuners
    Software
        oh esses
        dee bees
        solitaire
    example 2

    counting as well as displaying details --
    Code:
    SELECT COUNT(*) FROM daTable;
    SELECT * FROM daTable;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    example 1

    a printout that is supposed to look like this --
    Code:
    Hardware
        motherboards
        sound boards
        tuners
    Software
        oh esses
        dee bees
        solitaire
    example 2

    counting as well as displaying details --
    Code:
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ShinVe View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Nov 2011
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it. So if a php-specific function exists to get some (aggregate or meta) information from the db, use that.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •