SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best Practice: PHP vs MySQL

    I had trouble finding an answer to this. Is it better to have PHP or MySQL calculate dates?


    E.g.

    You want to select all entries that were created between {date} and 5 days after...

    Should you have php calculate date+5 days or should you let mysql do it?


    What's faster?

    My guess would be the PHP compiler would be faster - but what's the best practice with this?

    (I don't care about the code, I'm interested in the theory).
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  2. #2
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,713
    Mentioned
    102 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by jeffvdovjak View Post
    I had trouble finding an answer to this. Is it better to have PHP or MySQL calculate dates?
    Since the calculation work can be performed by either PHP or the DB, restrict the number of times you touch the DB to a minimum.

    Due to that type of consideration, if the calculated date is calculated using dates already in the database, and is used as a filter of some kind for when retrieving info from the database, it can be better to have the database perform the calculation work.

    In other words, where practical, use PHP.
    If you're already communicating with the DB and the date work can be easily incorporated in to the same request, use the DB.

    Try not to request dates from the DB, calculate using PHP, then request info using the calculated dates. That uses two trips to the database. Try to do it with just one trip to the DB instead.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  3. #3
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Paul.

    I would only make one round trip. I would either change the SQL to calculate the date or I would let PHP calculate the date. {date1} is provided by the user {date2} is calculated out as x number of days before or after (which is also provided by the user). In the past I've used PHP, but I know more about SQL than I used to. So when I started this project, I was just wondering which was is faster. We're expecting the database to be getting a lot of hits already, so I'm assuming that PHP would be the faster way to go. Also, I'm assuming that a database like MySQL would take more system resources than the PHP compiler...
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  4. #4
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Either one can be OK. The important thing is to keep calculations from being performed multiple times.

    This is fine:

    Code MySQL:
    SELECT *
    FROM SomeTable
    WHERE dateField >= '2011-3-13'
    AND dateField <= DATE_ADD('2011-3-13', INTERVAL 5 DAY)

    as is this:

    Code MySQL:
    SELECT *
     FROM SomeTable
     WHERE dateField >= '2011-3-13'
     AND dateField <= {php_calculated_date}

    If you are only doing one date calculation there will be (virtually) zero difference in performance.
    aaron-fisher.com - PHP articles and more

  5. #5
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks aamonkey. That's exactly what I wanted to know!
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  6. #6
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are only going to use MySQL then sure, let MySQL handle dates but what if at any point you decide to replace MySQL with a different database? No all databases have the same Date/Time functions as MySQL. So for the sake of portability handling dates in your program instead of in the database is a better approach.

    At the end it's up to you - they are equally fast, MySQL dates are not faster than php dates functions.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How does this:
    Code sql:
    SELECT *
    FROM SomeTable
    WHERE dateField >= '2011-3-13'
    AND dateField <= '2011-3-31'
    perform as compared to this?
    Code sql:
    SELECT *
    FROM SomeTable
    WHERE dateField BETWEEN '2011-3-13' AND '2011-3-31'
    Also, isn't it best-practice to cast your values as dates?
    Code sql:
    SELECT *
    FROM SomeTable
    WHERE dateField BETWEEN CAST('2011-3-13' AS DATE) AND CAST('2011-3-31' AS DATE)

  8. #8
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In regards to dates though, not all databases handle the dates the same way --- so the code would have to be changed as well.

    Right now I have models interacting with the database, so it'd be only minor changes to move to a different database setup.




    Quote Originally Posted by lampcms.com View Post
    If you are only going to use MySQL then sure, let MySQL handle dates but what if at any point you decide to replace MySQL with a different database? No all databases have the same Date/Time functions as MySQL. So for the sake of portability handling dates in your program instead of in the database is a better approach.

    At the end it's up to you - they are equally fast, MySQL dates are not faster than php dates functions.
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  9. #9
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you just store the dates in unix timestamp then the code does not have to change in case you change the database. Unix timestamp is just an integer and therefore very easy to use in the query using greater than or less than operators. All databases support < and > in queries as far as I know.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  10. #10
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lampcms.com View Post
    If you just store the dates in unix timestamp then the code does not have to change in case you change the database. Unix timestamp is just an integer and therefore very easy to use in the query using greater than or less than operators. All databases support < and > in queries as far as I know.
    Best to pass in a unix timestamp into your SQL then, e.g. " WHERE date > 1234512345" vs. " WHERE date > '2011-12-12'"?

  11. #11
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,713
    Mentioned
    102 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by transio View Post
    Best to pass in a unix timestamp into your SQL then, e.g. " WHERE date > 1234512345" vs. " WHERE date > '2011-12-12'"?
    It works either way, but I prefer to use yyyy-mm-dd for the dates as they are understandable when you view the data, and <> comparisons still work as intended.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  12. #12
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by paul_wilkins View Post
    It works either way, but I prefer to use yyyy-mm-dd for the dates as they are understandable when you view the data, and <> comparisons still work as intended.
    So the DB casts them prior to comparison... I always have a fear of the DB doing the opposite and casting the stored date to string for comparison... I never trust MySQL to do the right thing lol.

  13. #13
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,713
    Mentioned
    102 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by transio View Post
    So the DB casts them prior to comparison... I always have a fear of the DB doing the opposite and casting the stored date to string for comparison... I never trust MySQL to do the right thing lol.
    Take for example: comparing as strings '2011-03-21' and '2011-04-05'

    '2011-03-21' > '2011-04-05' is false, so things still work as expected.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  14. #14
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by paul_wilkins View Post
    Take for example: comparing as strings '2011-03-21' and '2011-04-05'

    '2011-03-21' > '2011-04-05' is false, so things still work as expected.
    Yes, but string comparisons are a significantly slower operation than int comparisons.

  15. #15
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For a select query, if you use PHP to set explicit date strings in your query then the MySQL query cache can store the result.

    If you do the date maths directly in the SQL then the query won't be cached.

  16. #16
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    Yes, but string comparisons are a significantly slower operation than int comparisons.
    In most cases a timestamp isn't appropriate as an output (and in this example we're looking at user interaction with dates) -

    would it not be faster to do a text comparison then to convert a string to timestamp, make the comparison, and the convert the resulting timestamp back to a string.
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  17. #17
    SitePoint Addict
    Join Date
    Nov 2008
    Location
    Peterborough, Ontario, Canada
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cranial-bore View Post
    For a select query, if you use PHP to set explicit date strings in your query then the MySQL query cache can store the result.

    If you do the date maths directly in the SQL then the query won't be cached.
    I was thinking this too...
    Have I helped you? You could help me.
    Like my business on facebook!
    Text message polls with real-time, live results.

  18. #18
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think that if a query contains functions then it does NOT get cached.

    EDIT:
    Ah sorry I was too late.


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
  •