3

I'm trying to determine if it's better to store an image's last modified date in a MySQL database or use the PHP function filemtime.

In my case, all of the website info is stored in a database (cms), so there is always a query to pull the image path, etc. The question is for caching purposes I need to have my HTML output something like this <img src="/img/photo.jpg?v20190613" />. From what I read on the php.net website this function is cached. So would it use fewer resources to add a field to the database table that stores the last updated timestamp or use this function each time? Are there any advantages either way? I'm looking for whatever is going to give the best performance.

YanDatsiuk
  • 1,885
  • 2
  • 18
  • 30
drooh
  • 578
  • 4
  • 18
  • 46
  • 4
    if your already accessing the DB, then storing the date in the DB would be faster, but by faster, we are talking an insignificant number of microseconds. –  Jun 13 '19 at 21:40
  • in my case, someone using the cms updates some info and there is an image involved, the image may retain the same file name and then on the front end they still see the old image, I could add a field to the db to note the updated timestamp, then simply append that to the image as I have outlined above. – drooh Jun 13 '19 at 21:44
  • 1
    If you are accessing the file info from database, it is clear faster. Just consider that reading last modified time attribute from the file system means one more access to the disk. – Marcel Preda Jun 13 '19 at 22:14
  • I plan to add an illustrative example of how I solved this, I would like to see other real world scenarios where developers are faced with using filemtime vs last updated database field. – drooh Jun 15 '19 at 19:23

3 Answers3

8

I wouldn't use filemtime(), for one specific reason: it only works for checking local files on the same host where the PHP code is running.

It's common in modern app deployments that you deploy PHP code to a different host than the one that serves static resources. In fact, it's typical to deploy PHP code to multiple app servers behind a load-balancer, so you can do rolling deployments without suffering any downtime.

You might not have this architecture today. You might deploy PHP code to the same host where your static files live, and your database too. But as your app outgrows a single host, or needs to have uninterrupted operation during deployments, you'll eventually need to scale out to multiple hosts. It would be better to plan for that early, and don't implement code that prevents you from scaling out if you can avoid it.

None of the PHP app servers would have direct access to the filesystem where the static files are kept. If they did, you'd have to do one of the following:

  • Store duplicate copies of the static files on each PHP app server, using lots more storage space. Then worry about keeping them in sync, have some back-end scripts to continually check that all hosts have the same set of files, etc.
  • Make the filesystem for static files remotely mounted to all the PHP app hosts, via NFS or similar protocol. Then the filemtime() checks would become somewhat slower, because they're going over NFS. And you'd have to worry about NFS mounts going away, security enforcement, configuring NFS when you add a new app host, and so on.

For these reasons I'd choose to put the timestamp in the database, since you already have file metadata (the pathname) stored there anyway.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • this is a really excellent point, realizing that it may not apply on most small scale situations. so for small applications if your already doing a sql query then it seems fit to place the timestamp in the db as opposed to running filemtime(). – drooh Jun 19 '19 at 06:18
0

Using MySQL database is mostly better

MySQL does and can do BST indexing and Query Caching, which is a luxury not always available to a filesystem.

And looking into your particular case, saving timestamp in the database would be much faster, because timestamp would be stored with the image path, so you probably get both timestamp and filepath in a single go, on the other hand using filemtime would be costly because php would request filesystem, and filesystem would search the file to give you it's timestamp.

Aurangzeb
  • 1,537
  • 13
  • 9
0

I stopped using the uploaded's filename as the filename on the server. When I did this, crucial files have been overwritten (e.g. PHP, CSS, etc.). So I add a random string to filenames when saving files.

With this, every filename is unique, so:

  • Files are not overwritten.
  • Filenames are unique so they are not cached.

Hence, you won't fall into your problem with adding a string to your image's URL.

By the way: For CSS and JS files I would also add random strings to filenames instead of adding "GET parameters".

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Raul Pinto
  • 1,095
  • 8
  • 15
  • I hear ya, this is how I was doing it before, but I wanted readable real world file names with nothing added, and for seo. This proved to be a big task but in the end was doable. Function to check duplicate file names and append with a numeral example Electric Guitar1.jpg and so on. The ?v20190820120000 helps control cache – drooh Aug 21 '19 at 03:58