Comparison between storing images/files in mySQL and on filesystem.
There was a discussion in regard to “
Test run are ran on following specs:
CPU: Intel 4 3.06GHz
RAM: 1 GB.
Linux Distribution: Ubuntu 6.2 Server edition
mySQL version 5.0.45
PHP-cli 5.2.3
The image file is 2.4MB, in total there is 1000 copies in the database. Overall, the physical size of the database file is 2.5GB.
The database structure contain 2 tables, image and imageblob.
- Table image have id, name, imageid and imagelocation as it’s field.
- Table imageblob have id and image as it’s field.
Let me explain what each field contain;
- image.id – primary id, integer type, unique and incremental.
- image.name – varchar type.
- image.imageid – forgein key to imageblob table, map to imageblob.id, indexed.
- image.imagelocation – text type, to store path of the image location.
- imageblob.id – primary id, integer type.
- imageblob.image – mediumblob type, this is where the binary data is stored.
Why did I have 2 table? I will let tell you later.
So first thing I do is insert the image file into those 2 tables.
mysql_connect("localhost", "test", "xxxxxx"); mysql_select_db("mysqltester_blob"); $filecontent = file_get_contents("map_large_community.png", FILE_BINARY); $imgid = 0; for ($i = 1; $i < 1000; $i++) { $imgid = $i; mysql_query("INSERT INTO imageblob (id, image) VALUES (" . $imgid . ",'".mysql_real_escape_string($filecontent)."')"); mysql_query("INSERT INTO image (name, imageid) VALUES ('testing".$i."', " .$imgid. ")"); }
I ran the above script 3 time to profile the performance. 1000 records were insert into the table in 2minutes and 38 secs. That translate to 0.158sec per record. Not bad, for each record containing 2.4MB worth of data.
Next step is to do the selection from those table. I will need 2 PHP script,
- Will query the table and retrieve the image from the file system.
- Will query the table and retrieve the image from the database.
————————-
mysql_connect("localhost", "test", "xxxxxx"); mysql_select_db("mysqltester_blob"); for ($i = 0; $i < 10; $i++) { $result = mysql_query("SELECT image.id, name, imagelocation FROM image WHERE name = 'testing".rand(1,500)."'"); $output = file_get_contents("map_large_team.png", FILE_BINARY); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { printf("ID: %s Name: %s Size: %d\n", $row[0], $row[1], strlen($output)); } }
This php script will retrieve image from the file-system after database query. It ran 3 times and the results are:
- 0.144sec
- 0.144sec
- 0.144sec
————————-
mysql_connect("localhost", "tester", "xxxxxx"); mysql_select_db("mysqltester_blob"); for ($i = 0; $i < 10; $i++) { $result = mysql_query("SELECT image.id, name, image FROM image inner join imageblob on image.imageid = imageblob.id WHERE image.name = 'testing".rand(1,1000)."'"); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { printf("ID: %s Name: %s Size: %d\n", $row[0], $row[1], strlen($row[2])); } }
This php script will retrieve image from the database. It ran 3 times and the results are:
- 1.389sec
- 1.337sec
- 1.256sec
————————-
As you can see from the result, the difference of performance very minimal. At the beginning of the test run, I was using 1 table to store both image detail and image blog. The performance was extremely poor, a similar SELECT query on name took a few minute to complete. I think it is because mySQL is going through all the huge blob column while search for the matching name. Splitting image detail and the image content help the query speed up tremendously. That is why I have split the data into 2 tables. Another important tip is image.imageid and imageblob.id must be indexed, it also help with the query.
In conclusion, we as programmer should see store large document/image in database as a solution. It’s performance is comparable to storing large document/image on file system. There are many advantage for store large data on database. At the end, as long as the table is structure correctly, the performance issue will not be an issue.
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Interesting. I like these test runs.
But as with others, I still think uploading to the filesystem is better, because it is easier.
“uploading to the filesystem is better, because it is easier.”
Not always the easier solution is the best one. In the long run, these turn to be chaotic.
[...] i might try this option for a bit and see how it goes. Test shows no performance hit. Comparison between storing images/files in mySQL and on filesystem. | Sitek Technology Blog __________________ My shitlist.. Are you on [...]
I dunno – that’s almost a magnitude slower grabbing the file from the DB. I like the idea of storing images in DB as well, but a simple filesystem tree sure seems easier and faster. I’d like to have the second for faster response or a bit more cushion in response.
good site oucbre
Hi
Please i can do with your help
how do i join the below cf statement so they are one
INSERT INTO Artist
(images)
VALUES ()
INSERT INTO artists (CompanyName, CompanyTelephone)
VALUES (’#formData.CompanyName#’, ‘#formData.CompanyTelephone#’)
My objective is to see that images end up in same colume as companyname and telephone
thanks in advances
Regards
I have to add 25 – 35 MB of data , i have tried everything and mysql 5.0.17 dosent loads more than 1 mb of data.
also i have checked for maximum allowed packet size and i am trying to insert data less (8000 of size) and then trying to append data to the same field by using update query.
UGRENT help needed
In conclusion, we as programmer should see store large document/image in database as a solution. It’s performance is comparable to storing large document/image on file system.
How do you draw that conclusion from your test as it is 10 times slower???
We will put this logic in our projects. Get back you soon.
You built some beneficial details there. I did a look for within the matter and observed most folks will consent with your webpage.
I have got very valuable information on storing image files in mysql.
Hi, My name is Cathy Lai and I am now in Johor Bahru, Malaysia. Have you came across any website that is of the same quality as this ESL learning site. I am not paying for anything, please let me have the link to any such free resource. Much appreciated.
Hi, My name is Christoper Lee and I am now in Washington. I hope someone has seen any website that is a resemblance of this ESL learning site. I have no money to spend on essays, please let me have more information on any such free sites. Much appreciated.
Hi, I am Albert Wee and I am now in Canada. Have you seen any essay directory that is of the same quality as this ESL learning site. I am not paying for anything, gladly thankful if you let me have the link to any such free directories. Sincerely.
I am new to this forum, My name is Cindy Choo and I am now in South Africa. Maybe you have came across any website that is similar to this ESL instructional site. I am not paying for anything, so let me have more information on any such free directories. Cheers.
Hello, I am Susanna Luo and I am now in Washington. Maybe you have visited any resource that is similar to this ESL teaching site. I have no money to spend on essays, please let me in on any such free resource. Cheers.
Hey guys, I am Sunny Kuo and I am now in Bangkok. Has anybody known of any resource that is just like this ESL learning site. I do not want to spend money, appreciate if you let me have the link to any such free directories. Write me.
Hi, I am Bernard Wang and I am now in Shanghai. Maybe you have visited any site that is similar to this ESL instructional site. I do not want to spend money, please let me know of any such free sites. Thanks.
Hello, I am Susan Kuo and I am now in Washington. Has anybody came across any website that is quite the same as this ESL teaching site. I am not interested in buying essays, appreciate if you let me have the link to any such free resource. Much appreciated.
Nice to be here, I am Jeremy Liu and I am now in San Francisco. I hope someone has known of any website that is of the same quality as this ESL instructional site. I am not interested in buying essays, please let me in on any such free sites. Write me.
“the difference of performance very minimal”? It’s nearly x10 slower from the database than file system.
Nice to be here… My name is Terry Sim and I am an English tutor located in Quebec. I’d prefer to be direct. I definitely needed more inbound links to my sample essay resource site and I hope you can allow my link here. This website is a free site which helps non-native speakers on the fundamentals of essay writing and I am without doubt that ESL students will learn from it. Again, I hope you will not delete my thread, and I thank you in anticipation.
I have been using Host Gator for three years. I manage 16 blogs on 15 different domains using one of their 4.95 per month plans. They give you more than enough of everything for even your above average user. You can get almost 10 dollars off of your service with the HostGator coupon code ninenintyfouroff
If you like crafts, you want to see a number of my different articles! Listed here are a few of my favorites:
A licensed tax attorney can greatly make a difference. Other than like, if you are audited by IRS, he can help you defend yourself for your best interest. I believe the tax deadline for 2011 is April 18th instead of April 15th so you have 3 more days to get yourself prepared.
;;` I am really thankful to this topic because it really gives great information ”:
Nice website you have here… though I have a question I’d like to ask you, ideally by email. How can I reach you?
Hello, i think that i saw you visited my blog thus i came to “return the favor”.I am attempting to find things to enhance my website!I suppose its ok to use a few of your ideas!!
Well, I have been reading your blog posts daily and the reason I come on your blog frequently is its compelling content
Haha you made my day! Nice touches to your creative writing style.
It’s laborious to seek out educated people on this subject, but you sound like you already know what you’re talking about! Thanks
[...] – Dateien in DB speichern? Heute, 22:08 Evtl hilft auch folgender Link ein wenig: Comparison between storing images/files in mySQL and on filesystem Delphi programming is like viral [...]
Aw, this was a really nice post. In idea I wish to put in writing like this moreover – taking time and precise effort to make a very good article… but what can I say… I procrastinate alot and under no circumstances appear to get something done.
Dell XPS M1210…
[...]just below, are some totally unrelated sites to ours, however, they are definitely worth checking out[...]…
You really make it seem so easy with your ppost!
I’m typically to running a blog and i really recognize your content. The article has really peaks my interest. I’m going to bookmark your web site and keep checking for brand new information.
48. magnificent post, very informative. I wonder why the other specialists of this sector do not notice this. You must continue your writing. I’m confident, you’ve a huge readers’ base already!
I like your post. Good job!
I not to mention my friends appeared to be checking the best hints found on the blog while suddenly developed an awful suspicion I never thanked the blog owner for those strategies. Most of the young men are already for this reason passionate to read them and have in effect certainly been enjoying those things. Thanks for really being indeed accommodating and then for choosing some nice information millions of individuals are really wanting to discover. Our sincere apologies for not expressing gratitude to sooner.
Thanks for all of your effort on this web page. Betty delights in conducting internet research and it’s easy to understand why. I know all concerning the powerful manner you convey practical secrets through the web site and therefore strongly encourage participation from website visitors about this concern plus our favorite simple princess is without question discovering a whole lot. Have fun with the rest of the new year. You’re the one doing a fabulous job.
It’s Hands made quality services that presents an fashion accessory its must-have desirability. This actually is evidenced in minimalist carefully crafted bag which are super understated within just their particular easiness connected with great-looking pattern.
I precisely wished to appreciate you again. I am not sure what I might have worked on without the entire techniques discussed by you about that industry. It has been the fearsome crisis in my opinion, nevertheless coming across the skilled fashion you handled that made me to jump with delight. I am happy for your work and in addition hope that you recognize what a powerful job your are putting in teaching people with the aid of your site. Probably you have never met any of us.
SEO experts in Perth…
[...]Comparison between storing images/files in mySQL and on filesystem. | Sitek Blog[...]…
alcoolisme et impuissance cancer de la prostate levitra pharmacie dysfonction erectile, citrate de sildenafil pharmacie depression avant impuissance? impuissance feminine dysfonction erectile [url=http://www.psy-angers-carl-rogers.sitew.com/]prix viagra achat[/url] prix viagra 100mg cozaar et impuissance merck sharp & dohme. l impuissance sexuel hypertension arterielle homeopathie impuissance dysfonction erectile viagra 50mg sans ordonnance viagra authentique impuissance masculine.
outstanding post! great advice, will take on board!vaw
viagra…
[...]Comparison between storing images/files in mySQL and on filesystem. | Sitek Blog[...]…
[url=http://tm-stroi.ru/dekorativnaya-shtukaturka/opisanie.html][img]http://s012.radikal.ru/i320/1112/5b/5919d02f5246.jpg[/img]
[img]http://s47.radikal.ru/i118/1112/cd/2e0462ac92b8.jpg[/img][/url]
Tegs: Декоративная штукатурка Щебень [b]Природный камень Прайс-лист[/b].
[u]декоративная штукатурка способ нанесения видео мастеркласс [/u]
[i]штукатурка мраморная крошка декоративная капарол [/i]
[b]ооо агат декоративная штукатурка [/b]
[url=http://tm-stroi.ru/prirodnyjkamen/prajs.html]Природный камень Прайс-лист[/url]
You commit an error. I can defend the position.
You didn’t set image.name as an index, that is your primary reason for loss of performance, since you are doing a query by this field
I’d love to see corrected results-but I’m going to go run that test myself, with proper indexing
0.0284 seconds per image from the db using proper indexing
I used a database twice as large, with an index on image.name, and otherwise kept the same test instructions