0

I have a simple user comment system. These can come with an image. Currently I am storing user uploaded images in a folder. I do not write anything to the DB for the image, very simply I name the image file with the comment id (only one image per comment allowed) and store them all in one folder:

move_uploaded_file($upload_pic,'images/'.mysql_insert_id().'.jpg');
//please note I am also upgrading to mysqli

then while retrieving comment data from database I am checking with file_exists() and potentially displaying the full image with the comment.

if (file_exists('images/'.$fid.'.jpg')) echo '<img width="576" src="images/'.$fid.'.jpg">';

Now I want to change it to be able to store and show more images per post (still limited to 4) and have also thumbnails.

Here comes the question

For such simple system is it faster to store all file references in a separate table in the database or enhance the file naming related to comment ID ?

  1. If database - how to name the files ? using uniqid ? Simple mapping table id, imgname.
  2. If file naming I was thinking of XXXXXX-Y.jpg and retrieving with glob("images/".round(XXXXXX/1000)."/XXXXXX-*.jpg"), having max 1000 files in one folder. (currently there's 6.5k images in 105k posts over last 10yrs)

To me the file naming seems more simple as I do not need to store any additional information related to the image, but on the other hand it seems majority of people uses database. And would like to focus on the way with better performance.

Community
  • 1
  • 1
John
  • 123
  • 1
  • 10

1 Answers1

0

In my experience from working on a real estate website its quite expensive to check for file exists.

So if you have a table with file names in database that will much faster to display. You will not be checking explicitly for file_exists for all the four files every time, you will just display the image tag as many times as you have images for that comment in db. Like if you have 1 image for a comment you will not need to check for the other 3, you only found 1 from db, so you just display it at once.

For the naming, keep things simple like using this:

3673-1-originalfilename.jpg 
3673-2-originalfilename.jpg
3673-3-originalfilename.jpg

which follow the following pattern

"commentid"-"count"-"original-filename"."extension"

Needless to say but you can store more data about the images in the database, if you have to like the thumbnail file name as well or file size etc, and you can store any number of images for a comment as you want.

Hope this helps!

rashid
  • 306
  • 3
  • 12
  • Thanks Rashid, I think it makes sence. I don't like the idea of storing image in database, but I will focus on the mapping table. Do you think I shall split the files into more folders as now I am having about 6500 files in one folder. – John Nov 10 '14 at 12:34
  • Once you don't look up anymore if the files exist, the number of files in the folder has much less impact on the performance. Probably you will not need anymore to split them in folders. – Lorenz Meyer Nov 10 '14 at 19:26
  • @John Like Lorenz said, it wont have any substantial affect on the performance but if it helps you for your own management than its a good idea to make folders. (But you'll need to know in which folder the image resides) – rashid Nov 11 '14 at 08:15
  • Many thanks to all of you for you comments, I will keep all images in one folder for now. – John Nov 14 '14 at 13:19