Hello,
I have a ''users'' table which saves some user specific stuff
like email
address, {first,last}name, birthday,...
And there is also a column ''image'' which holds an image of the
user:)
After filling the database with a lot of users and especially images the
request response of all pages related to querying users from the
''users''
table goes down.
I guess it was a bad design decision to store the binary data in the same
table as the user informations that are queryed a lot?
Would it be better/the preffered way to save the images on the filesystem
directly and only save a ''file reference'' (blub.png) in the
image column? I
guess this is what the file_column extension does.
Would a separate table for the user images also be a viable alternative to
speedup the whole thing?
Grettings,
Robert
--
http://www.robert-gogolok.de
JID: gogo-2ASvDZBniIcGMNGl0kwjzRvVK+yQ3ZXh@public.gmane.org
Robert Gogolok wrote:> Hello, > I have a ''users'' table which saves some user specific stuff like email > address, {first,last}name, birthday,... > > And there is also a column ''image'' which holds an image of the user:) > > After filling the database with a lot of users and especially images the > request response of all pages related to querying users from the ''users'' > table goes down. > > I guess it was a bad design decision to store the binary data in the same > table as the user informations that are queryed a lot? > > Would it be better/the preffered way to save the images on the filesystem > directly and only save a ''file reference'' (blub.png) in the image column? I > guess this is what the file_column extension does. > Would a separate table for the user images also be a viable alternative to > speedup the whole thing? > > Grettings, > Robert >New to rails myself, but I''m thinking that rails in no diffrent than anything else at this. I''ve always just hashed (sha1) and stored the hash of the file in the database and then saved the file on the server this file name as the hash. create table users ( id int(11) NOT NULL auto_increment, lock_version int(11) default ''0'', name varchar(20) NULL, email varchar(50) NULL, image varchar(40) NULL, primary key (id) ) ENGINE=InnoDB; Then require ''digest/sha1'' filename = ''xyz'' user = User.new user.image = Digest::SHA1.hexdigest(File.new(filename).read) File.rename(filename, user.image) Just an idea, hope it helps a little. David.
Robert Gogolok wrote:> Hello, > I have a ''users'' table which saves some user specific stuff like email > address, {first,last}name, birthday,... > > And there is also a column ''image'' which holds an image of the user:) > > After filling the database with a lot of users and especially images the > request response of all pages related to querying users from the ''users'' > table goes down. > > I guess it was a bad design decision to store the binary data in the same > table as the user informations that are queryed a lot? > > Would it be better/the preffered way to save the images on the filesystem > directly and only save a ''file reference'' (blub.png) in the image column? I > guess this is what the file_column extension does. > Would a separate table for the user images also be a viable alternative to > speedup the whole thing?I would take this a step further and say it''d be best if you created an images table. Whether you decide to store the image as a blob or on disk, put the images in their own table. This way you can give your users table an "image_id" which points it to an image. If you ever get to a point to where other tables will have images, you can give them "image_id" and foreign key constraints to. This will give you more flexibility, and a better storage solution IMO for images. If you have the Agile Web Development With Rails book I would suggest start reading on page 362 with "Uploading Files To Rails Applications". They use a "pictures" table, but it is the same type of thing you''re looking to do. HTH, Zach
Robert, On 10/31/05, Robert Gogolok <gogo-XEKLf8KCelQbFoVRYvo4fw@public.gmane.org> wrote:> Would it be better/the preffered way to save the images on the filesystem > directly and only save a ''file reference'' (blub.png) in the image column? I > guess this is what the file_column extension does. > Would a separate table for the user images also be a viable alternative to > speedup the whole thing?I chose the filesystem design for file_column because rails is not very good at handling blobs right now (it always loads them into memory completely, I believe) and storing them on the filesystem is pretty easy and reliable anyways. Plus you can let your webserver serve the static image files directly (if you do not need authentication for them), which should be way faster than rails could ever do this. Sebastian
On Monday 31 October 2005 11:08, Sebastian Kanthak wrote:> I chose the filesystem design for file_column because rails is not > very good at handling blobs right now (it always loads them into > memory completely, I believe) and storing them on the filesystem is > pretty easy and reliable anyways. Plus you can let your webserver > serve the static image files directly (if you do not need > authentication for them), which should be way faster than rails could > ever do this.You could cache the images also with rails so that they are served directly. I will try the file_column suggestion, tanks for the suggestions! Robert -- www: http://www.robert-gogolok.de JID: gogo-2ASvDZBniIcGMNGl0kwjzRvVK+yQ3ZXh@public.gmane.org Tel: 0681 / 302 4135
> On 10/31/05, Robert Gogolok <gogo-XEKLf8KCelQbFoVRYvo4fw@public.gmane.org> wrote: > > Would it be better/the preffered way to save the images on the filesystem > > directly and only save a ''file reference'' (blub.png) in the image column? I > > guess this is what the file_column extension does. > > Would a separate table for the user images also be a viable alternative to > > speedup the whole thing? > > I chose the filesystem design for file_column because rails is not > very good at handling blobs right now (it always loads them into > memory completely, I believe) and storing them on the filesystem is > pretty easy and reliable anyways. Plus you can let your webserver > serve the static image files directly (if you do not need > authentication for them), which should be way faster than rails couldThat''s what is done for my auth_generator available at http://penso.info/rails/auth_generator/ Images are stored in the user model database, but served directly using caches_page so no performance problem. User images/icons are usualy pretty small in size anyway.