Best Practice: PHP vs MySQL

PHP
1

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).

2

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.

3

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…

4

Either one can be OK. The important thing is to keep calculations from being performed multiple times.

This is fine:


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

as is this:

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.

5

Thanks aamonkey. That’s exactly what I wanted to know!

6

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.

7

How does this:

SELECT *
FROM SomeTable
WHERE dateField >= '2011-3-13'
AND dateField <= '2011-3-31'

perform as compared to this?

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?

SELECT *
FROM SomeTable
WHERE dateField BETWEEN CAST('2011-3-13' AS DATE) AND CAST('2011-3-31' AS DATE)
8

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.

9

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.

10

Best to pass in a unix timestamp into your SQL then, e.g. " WHERE date > 1234512345" vs. " WHERE date > ‘2011-12-12’"?

11

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.

12

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

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.

14

Yes, but string comparisons are a significantly slower operation than int comparisons.

15

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

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.

17

I was thinking this too…

18

I think that if a query contains functions then it does NOT get cached.

EDIT:
Ah sorry I was too late.