Hi peeps,
I'm planning to design a friendster-like website for our school. I thought of storing images in mysql but am also thinking how much a mysql databse can contain. also, how long would it be for a query on a blob field to return results e.g. opening a page which images are stored in the mysql databse.
thank you very much, your advice is highly appreciated.
It depends mainly on your host:
- how fast (or slow) is the mysql-server?
- how much space do you have for your dbases?
- how much diskspace do you have?
Storing the images in files will almost always be faster, but if your dbases may as big as needed and your diskspace is limited it might be better to store the images in the dbase.
The best way to test the speed would be to make a small script which extracts a large amount of pictures from a dbase and measure the speed. Do the same for images from files.
personally I would not store the images in the database, rather, store the file location and extension in the database that way it can call the image faster.
example.
Folder on your root directory called /pics
what you would want to do is setup your script to save the variable of pics/imagename.(whatever the filetype) into the database and call the images that way. rather than doing a BLOB in your database. BLOBS are good for smaller applications, but what you are talking about sounds more of a user load heavy app and it will slow down alot.
Blob ( Actualy long blob to be precise)is a good solution if you are using small pictures like PP size photos. Also it is useful if you want some kind of data mining or OCR application.For the general purpose use any galary script or a separate folder to store image and put their references to data bases. Also some servers put a restriction on the size of the image to maximum of 1-2 MB.In that case storing high reselution images will not be possible.Consider your actual requirements and take decisions accordingly.
why use mysql to store images? mysql can't really improve anything that the file system can do with images.
If it's normal text data, mysql might improve searching but I don't think images are useful to use up your mysql connections
I considered trying to do the same thing with my site's thumbnails for images, but in the end (the images are remotely served), I figured that it was best to just store a few details about the image in the database, and the filename. My query will bring back all details and my php code will create the html that contains references to the images using my values from the database for width, height.
This way, I only ever call getImageSize and do the average color calculations once per image.
I also store the image's average color - and use that as the border - and have a div that is the same color blended 50% with white -- to produce a nice effect.
| Mgccl wrote: |
why use mysql to store images? mysql can't really improve anything that the file system can do with images.
If it's normal text data, mysql might improve searching but I don't think images are useful to use up your mysql connections |
just thinking, when uploading picture in directory, not in mysql, what if user upload a picture with the same name ? the old file will be replaced. but if using mysql we can prevent this.
regards,
-awi- | meejoku wrote: |
| Mgccl wrote: | why use mysql to store images? mysql can't really improve anything that the file system can do with images.
If it's normal text data, mysql might improve searching but I don't think images are useful to use up your mysql connections |
just thinking, when uploading picture in directory, not in mysql, what if user upload a picture with the same name ? the old file will be replaced. but if using mysql we can prevent this.
regards,
-awi- |
You have to use a script to let users upload pictures anyway, so you can let the script check for duplicate filenames.how do will i put the directory of the image in the mySQL database?? Is it the same as windows like C:\sample folder\images.jpg????
You have to use the reletive path, not absolute path.For example if your
main directory is project(which contains all the scripts) and a sub-directory image, then simply refer the image directory to store your image. The directory refers to the directory you are keeping in htdocs.
Yeah, just set up a table in a db called Images or something and have columns called id, filename etc. You don't have to store the location in the db though, as long as you have a set location that the images are stored at, then you can just hardcode the image location in your php. It is definitely more wise to not store the files in the db though, just keep the filenames in the db.
Anyway, good luck with the site, it sounds like a fun project.
