Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Database Info

  • Database Type - Percona MySQL.
  • Table engine - InnoDB.

I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 encoded image in each row. I'm wondering if anyone else has ran into the same type of problem and what they decided to do about it. The options I'm currently thinking about are:

  • Method 1 Storing each image named by the row's unique id in some sort of cloud hosting server. This would fix my database size problem, but it would be a pain to manage because the images would be on a different server. Also pushing the images up to the cloud server would take longer than the current method.
  • Method 2 Compressing just that column on a mysql database level? (if that's possible? I'm not sure but I believe it is, still researching. I would assume this would slow down the insert and select statements also though.)
  • Method 3 Compress the base64 before sending it over to the database with something like LZString Compression. (I'm sure this would entail on my database level such as what field type to put this data in or character set/encoding would be needed. Possibly could combine method 2 and 3 together for maximum savings? not sure if that would work or if it would be possible)

If anyone could give me some information on what would be the best way for me to approach this dilemma or if anyone has run into this problem before I would really appreciate the any help/info.

Thanks in advance.

share|improve this question
    
Are you saying you are getting an error of "table size too large," or are you saying, "this table is larger than I want it to be?" –  Michael - sqlbot Feb 24 at 12:26
    
@Michael-sqlbot The ladder. The server I'm running this database on is only 40gb of space, and since this site's only been running for 4 months, something needs to change fast. –  Adam Merrifield Feb 24 at 16:44

1 Answer 1

up vote 2 down vote accepted

Base64 is an encoding for transferring data reliably over the wire. It usually increases a files size so I wouldn't look to store a file in this format.

BLOB
The first thing I would do is decode the Base64 back to the original file and store it as a BLOB field. This could shrink the files by a third.

Table design
You said each row has a 40kb image. Why are they all the same size? If some rows share the same image then you could put the images in a table of their own and reference them from your big table.

Limit filesize
Most sites that let you upload an avatar limit the filesize/resolution of the image. Without knowing what these images are for perhaps you could impose a similar limit. You could even rescale the image automatically when they upload it.

Sorry if these are already obvious to you. It's difficult to be more specific without knowing the background about what the images are for.

share|improve this answer
    
Images are avatars. They are built with a character builder which have a good amount of options, and will be growing. Which would mean creating a table of all options is possible and would be smaller than the current way. But, I don't believe creating a table of every viable option of character would be the best Idea, because I can't think of a good way to index such a table and select it with the options used on the avatar. Here is an example of an avatar. Because they're made with a builder limiting size doesn't apply. Thanks for the response though! –  Adam Merrifield Feb 24 at 22:51
    
I suppose my questions really are; 1.What is the best way(data type/encoding/etc) to save an image in a database. 2. Is it better to just save these images as files in a cloud and link to them? If so at what point is it better to use files instead of databases. –  Adam Merrifield Feb 24 at 22:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.