[SOLVED] what's better, 2 tables, or one large (in this situation)

[dj-kenpo]

ok, so I'm building a multi user site (for fun) and I on the blog and photo sections you can leave comments. right now I have 2 comment tables, one for blog, one for photos.

they're both the same. I'm worried about things once theres lots of entries, so what's optimal?

the 2 comment tables are exactly the same, should I have both, or have one comment table and then add an extra row to describe whether it's for the photo or blog.

 

thanks, sorry if this is an ignorant question, both ways work, but I'm wondering if there's a clear advantage. I very rarely need to call results from both at the same time.

[steelmanronald06]

leave the two different tables.

[MadTechie]

agrees, two heads are better than one  :-\

 

i mean two tables, basically its more flexable

[AndyB]

i mean two tables, basically its more flexable

 

You'll have to explain that one to me.  The tables both have identical format, and the addition of a single field (blog/photo) would eliminate any downstream maintenance issues with two tables.  One table also simplifies the uses where 'rarely' the OP wants data from 'both' - something that may become less rare as the site develops.

[MadTechie]

i am assuming that the photos database has a blog field and is storing the image data in their(not a link), this would make database quite large and i believe refering to it when needed would be better than using it with other more commonly accessed data.

[dj-kenpo]

I'm not storing anything large in the database, I don't use blobs. no blobs. it's just comments. name, email, comment.

 

my thinking is, it's easier for me to have one large table for these, the downside I see in that is that queries for specific , say blog comments have to be searched more. it's an extra where clause, I'm not sure if the impact of the where clause and extra indexing size is worth it. I wouldn't know how to begin comparing so I'm hoping someone here with experience in larger db management could say.

[dj-kenpo]

so ANdy B is suggesting one table, and you guys are suggesting 2? any oppinions on speed?

[MadTechie]

as i assumed you had blogs and you don't i have to move to Andy B side (if the tables are almost exact ie both alway have the same number of records etc and one isn't being used for a massive data hold)

[dj-kenpo]

cool, that makes joint queries where I want data from both faster,

 

but my only question then, what's the performance hit? it's an extra where clause, does it matter at all? is the difference so tiny I need not worry? again I'm thinking of the comment table once it hits say 400k records, and it now sorting what ones are needed and not, rather than not having to at all

[MadTechie]

you might want to read up on indexing

 

In general, when you want to make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. All references between different tables should usually be done with indexes.

[steelmanronald06]

Personally, I think two tables would be for the best. I mean your going to have to add an extra column to the one table so it looks like so:

 

comment_id, name, email, comment, for

 

Where the for field is either blog or photo.  Well, first your query is going to have to search through sum 1000+ records, assuming it gets that popular, and find all the ones for a blog entry or all the ones for a photo entry.  THEN, say you want to get rid of your photos section later on, or your blog section. All those comments will still exist, taking up space.

 

Now, you also have to look at something else. You only want to display the comments made for each blog entry or each photo. So your going to have to add another two fields, and both will have to be set to NULL.  Now your table has grown to look like this:

 

comment_id, name, email, comment, for, photo_id (NULL), blog_id(NULL)

 

So now your query will be like this to get a blog entry for a blog with id 6:

 

SELECT * FROM `table_name` WHERE `for`='blog' AND `blog_id`='6' ORDER BY comment_id DESC

 

Now your query above is going to go through thousands of comments to find about 5 comments for blog entry 6 and it is going to order them with the newest comments at the top and the oldest at the bottom.

 

Now, say you have it split into two tables. You get this:

 

blog:    comment_id, name, email, comment, blog_id

photo:  comment_id, name, email, comment, photo_id

 

Now your query just got smaller:

 

SELECT * FROM `blog` WHERE `blog_id`='6' ORDER BY comment_id DESC

 

Now you can choose in the future to get rid of one without harming the comments on the other, there is less your query has to sift through, AND the query itself is smaller. Plus, it makes it easier to tell things apart.

 

The entire point of is to keep things optimizied.  Granted it doesn't make a HUGE amount of difference at first, or even later on since MySQL is a powerful database, BUT the goal is to stay as organized and optimized as possible.

[MadTechie]

LOL, ok to sum up, it depends on the scale of the database

[Barand]

I'd say it depends on how the blogs and photos are stored. Are they in a single table or two?

[dj-kenpo]

blog entries and photos themselves are in 2 different tables.

 

I'm not worried about deleting, and if I did, I'd run an auto script and delete everythign where mode=blog or photo.

 

if it matters, the query also has User_ID, so it looks for all the comments for a certain user first, then wittles down by photo/blog then by entry.

 

 

I'm not sure if that makes it better or worse for say... lets imagine 400k rows....

[Barand]

Is there any relationship between the blog and photos, or is it just two separate tables.

 

I suppose I'm saying it's hard impossible to comment on a table structure when the data relationships are unknown

[dj-kenpo]

sorry.

 

no. there is no relationship between blog and photos. one is for posting a blog, the other is for a photo album, they look different.

 

but they both have the same comment system, and the table structure for the comments table is the same.

 

ie

journal_comments.id

journal_comments.rel_id

journal_comments.User_ID

journal_comments.name

journal_comments.comment

journal_comments.url

 

photo_album_comments.id

photo_album_comments.rel_id

photo_album_comments.User_ID

photo_album_comments.name

photo_album_comments.comment

photo_album_comments.url

 

hope that helps, I'm not trying to be cryptic, it's just sort of a more theoretical question than a code question.

 

speed is my main concern, but it seems silly to have too tables exactly the same

[dj-kenpo]

so in this case 2 would be better?

[Barand]

I'd go with 2 in this situation as they are completely unrelated. The only thing they have in common is layout and that could change in the future. For example, you may want to introduce comments/rating on differnent aspects of the images (composition, use of color etc) that certainly would not have any relevance to blog comments.

[dj-kenpo]

this is true, thanks barand. on a seperate but related question, (seperate as I'll stick with two) what's your estimate of the performance difference between having one or two, I have no idea how to begin gauging benchmarks like that (nor is the db large enough ). does an extra where clause make a huge performace hit even with indexing?

 

thanks!

[Barand]

My largest mysql table is only 10,900 records so I don't know what it would be on 400k. I'm getting differences of 0.02sec when selecting from 2 halves or all the table

[dj-kenpo]

hmmm, ok, thanks barand! that's really quick, so it really does just come down to scaling then. this was all so great, I'm glad this side and all of you (especially barand!!) are here