0

I have a search engine where each product item can have a thumbnail image for the summary and a larger image for the detail view.

Currently the image ids are stored as img_id and thumb_id in the Products table, and the attributes are kept in the Image table, (width, height, type) which needs a join to construct the image tag. Images are kept on a sub domain.

Products table has several million rows but not all products have images.

Should I do away with the image table and if so what method would you suggest to fetch the images?

Also there other smaller product catelogs being served on this system which have similar table structure

Thanks in advance.

I think what im looking for is here How to store images in your filesystem

Community
  • 1
  • 1
gus
  • 765
  • 3
  • 14
  • 26
  • Can you add definitions of your tables and the exact query which you use in your application? – Karolis Jul 07 '11 at 09:50
  • By the way, as I understood your main problem of slowness is `left join` of image table, yes? – Karolis Jul 07 '11 at 10:10
  • thats part of the problem. The other side of the coin is that im rewriting the image upload routine. Would save a lot of overhead/hassle if i didnt have to save off into image table a well each time a new listing is posted into the system. – gus Jul 07 '11 at 10:23

1 Answers1

0

You should think about the trade off between "optimal image saving" with different types (jpg, gif, ...) and different imageURLs against the need safe all this information separately. An alternative would be the following:

  • safe every image as jpg, for example
  • use as image name $rowID_thumb.jpg and $rowID.jpg
  • to avoid problems with your file system (over a certain value some file systems can't handle a huge amount of data) you could create a directory clustering, for example 1/123/1234/123467.jpg, where 1234567 is the $rowID
  • Think about fixed dimensions of your images to avoid the need to save width and height. There are enough image manipulation functions in php to get all the images to the same size

I would not store the name of the image in the db to save your independence ;-) In detail: you have a shop item with the ID 123. For this shop item exist an normal image an a thumbnail (only two images, otherwise my proposal is not the best solution!). I would not store anything about the images in the DB. Instead, use the ID 123 of the shop item to generate the filename programmatically, like getImagePathAndFilename($id) -> 1/123/123.jpg. In your upload process you have to take care that all the images have the same dimensions to avoid the need to save them separately.

strauberry
  • 4,189
  • 5
  • 34
  • 50
  • Yes i was thinking of saving every file as jpg.Thanks – gus Jul 07 '11 at 09:59
  • It also makes quite a difference when saving the listings as it creates a lot extra overhead and since im currently rewriting that module its another reason i thinking of changing... – gus Jul 07 '11 at 10:01
  • not really sure what you mean by "I would not store the name of the image in the db to save your independence". I would only be storing the actual file name not the URL. The Url is dynamic according to which directory the images are stored in. That way if the directory changes i can easily update the URL $link. – gus Jul 07 '11 at 10:42
  • im not following... if i change file type the new file name will include the extension as well eg.. < img scr = image_123_thumb.gif ..> will still render ok. I cant see the need to change the update the image name? FYI.. Currently, The image id is actually the row ID from the image table not the ID of the product. If i where to store the file name i would generate a random unique ID for the file name so something like thumb_a123423423.gif would be stored in the product Table ? – gus Jul 07 '11 at 11:05
  • Answered my text your question? If so, could you accept it :-)? Thank you! – strauberry Jul 08 '11 at 07:39
  • I think ill still need a reference somewhere because not all items have images.The images are kept on the sub domain,so id have to send the request and check if it existed. Not sure thats a good idea? What do you think. – gus Jul 09 '11 at 06:20
  • Not exactly what im going to do but on the right track thanks strauberry – gus Jul 10 '11 at 10:17
  • You're welcome :-) I just wrote the same answer in point 3 of my list... I thought it would be obvious that I mean a clustering by user id and so organizing images with this method. Sorry for being not exact enough. Lesson learned ;-) – strauberry Jul 10 '11 at 10:31