Will it be faster if I combine my detail rows into a single column in a header table?

Question

So I have an image gallery website organized in albums and so the db table storing images of the albums, has this scheme basically:

album_id | images_link
112      | 1.jpg
112      | 2.jpg
112      | 3.jpg
112      | 4.jpg
112      | 5.jpg
112      | 6.jpg
112      | 7.jpg
112      | 8.jpg
112      | 9.jpg

So in order to avoid a super-long database table I'm thinking maybe to store all the images of the albums to a single cell like so:

album_id | images_link
112      | 1.jpg, 2.jpg, 3.jpg, 4.jpg, 5.jpg, 6.jpg, 7.jpg, 8.jpg, 9.jpg

I mean the database size of course will remain the same but there will be significantly less rows to read. I'm going to use the explode function to split and serve each image file link. Is this a smart choice? I'm not sure if the explode function is memory as hungry as fetching a large database. I'd like to hear your opinion.

See the problem is the images names are not that short, images_link row is set to 27 chars so with a limit of 30 pictures per album that I have set, so a single cell is expected to reach 810chars to a later time. I've never used varchar to store that amount of bytes, is it better to use VARCHAR in this case or TEXT ? I know VARCHAR is faster.

Thank you in advance.

Not a good idea. If you want to remove a single image you have to explode, remove, implode, update instead of just a single `DELETE` command. The way it is at the moment is the correct way of doing this in SQL — Benjamin Paap, Dec 07 '12 at 22:15
Read this please: **[Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad)**. Short answer: **Yes, it's really that bad.** — ypercubeᵀᴹ, Dec 07 '12 at 22:29
And the similar: **[Is using multiple foreign keys seperated by commas wrong, and if so, why?](http://dba.stackexchange.com/questions/17808/is-using-multiple-foreign-keys-seperated-by-commas-wrong-and-if-so-why)** — ypercubeᵀᴹ, Dec 07 '12 at 22:29
There's a programming maxim that says "Premature optimization is the root of all evil." Putting your comma-separated list into a column is the epitome of premature optimization. You don't know what performance problems, if any, that you have. You are imagining that it will be slow. Your solution will make things much harder to maintain in the future. — Andy Lester, Dec 07 '12 at 22:38

score 3 · Accepted Answer · answered Dec 07 '12 at 22:20

3

Your first scheme is better than the second.

Is better for maintenance:

Only adding a row to put a new image
Only deleting a row to quit a image
if you want to edit the path of a image you only have to update that row...

An later if you want to list the galery with its first image you only have to do a JOIN...

With your second schema there's no differences in having a field in categories table called "list_of_images"...

answered Dec 07 '12 at 22:20

Pablo Martinez

2,172
1
23
27

Thanks for your answer. I get it that's better for maintenance but how about performance? – inrob Dec 07 '12 at 22:22
for performance is better, the databases system are prepared for do this – Pablo Martinez Dec 07 '12 at 22:25
Thank you again, if you don't mind taking a look at the question I asked to Andy Lester I would appreciate it. – inrob Dec 07 '12 at 22:29

score 2 · Answer 2 · answered Dec 07 '12 at 22:19

2

No, do not do that. There is no problem having a "super-long database table". There are many potential problems with you aggregating subitems into a single column.

You say "there will be significantly less rows to read." Are you imagining that there will be some sort of speed boost if you don't use a detail table, and you cram everything into a header table? There won't.

The way you have it now is the Way It Should Be Done. Keep it that way and keep learning.

answered Dec 07 '12 at 22:19

Andy Lester

91,102
13
100
152

Thanks for your answer/suggestion. – inrob Dec 07 '12 at 22:23
Besides becoming a long db the other 'problem' with it was that if the user upgraded to an upper plan (he'd be allowed to add more images per album). So if he initially createad an album with the id : 120 and 9 images like I explained above, He may want to add some more pictures, but there have been created let say 10k albums so the location of the first 9 images might be in the top of the database and the location of the extra/later added images will be in the 'bottom' of the database. Would it be smart to travel from the top all the way down to get the extra images? – inrob Dec 07 '12 at 22:25
@bornie: that's what indexes are for. – ypercubeᵀᴹ Dec 07 '12 at 22:31
I've implemented indexes so my idea of `travelling` is irrelevant :) Great. – inrob Dec 07 '12 at 22:34
1

Nearly all DBMS are a lot more than just a dumb pile of data. A lot of optimization is done to get the data as fast as possible (if you use indices). Don't worry too much about large tables :o) – Sir Rufo Dec 07 '12 at 22:40

score 0 · Answer 3 · answered Dec 07 '12 at 22:20

You should keep your original table structure as maintaining the images_link column will become a nightmare. What happens when you want to remove the row where album_id=112 and images_link=3.jpg?

If you want to simplify the read to a single row, try a query using group_concat

SELECT GROUP_CONCAT(images_link) FROM tbl_album_images WHERE album_id='112'

which should return

1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg

EDIT: I'm sure it's obvious, but also make sure you have an index on album_id

Yes I have an index. Thanks for your answer. – inrob Dec 07 '12 at 22:31 — inrob, Dec 07 '12 at 22:31

Will it be faster if I combine my detail rows into a single column in a header table?

3 Answers3