PHP filemtime vs MySQL last updated timestamp for image caching in CMS

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.