10 replies to this topic

[BASHERS33]

Is there any general rule of thumb how many rows is just flat out too big of a table? And what would be an ideal way to even handle it? I'm using mysql, if that matters.

I'm just really disappointed that I can't think of a good way to handle the amount of data I want to allow. In the past I had a field with a serialized array. Well some programmer said that it wasn't a good idea to have it thatw ay, especially since I would be doing various things with that data and would ideally like to order or group by a row of it etc...

He also said that it takes up less space if I put it into separate rows in a new table even though it would be adding a few fields. So I ended up doing it. But now I realized that at the capacity I am using the feature for each ONE row added, it could have 20 or 30 more rows added in this new table! So what if for some reason I sell it and it DOES get active on someone's site? What if there are 1 million rows? Then that means 20-30 million rows in the other table!!!!!!!!

At the very least I could divide the table into two tables because there are two types of rows in it anyway. It would just mean adding an extra query on a couple pages though. And it's just really disappointing that to have the feature the way I want it, it means this overload of data.

As far as space goes I could lower the space itself if I take a field from another table which has choices separated by commas and then I make a new table with them as rows and then in this table use numbers to match them up. But then THAT would make the php code worse than it already is and it's already almost twice as bad as forum index page query times, I believe.

Now with this feature it's really up to the admin to what capacity to use it. So an admin could either not use it at all and then none of these rows would be added or they could use 5, 10, etc.... types of things. But for me personally part of the uniqueness of the system is depth and so I am using a lot of choices which ends up being tons of rows.

I don't have an id field in this table because I didn't want to chance it hetting up over the limit that the field would allow for int. I don't need a field like that anyway.

Really the only thing I can think of is making it where the admin can delete some of the choices if their DB gets too big and then it will go through and delete all rows associated with those which were deleted. But this is pretty sad. I doubt many, if any, sites would have a HUGE number of rows. But just look at how it currently is... the main table has 17 rows and this table I am discussing already has almost 500 rows just for those 17! On a busy site 17 main rows would be added with the blink of an eye and boom 500 rows just like that.

[WingedPanther]

I think you're approaching it the wrong way.

Databases are intended to store large amounts of data. Different databases scale better than others (Oracle and MS SQL scale well, Access doesn't). The overall size of a database depends on several factors:
1) the type of data stored (BLOBs can bloat a database quickly)
2) the number of records
3) the number of fields per record
etc.

I've dealt with databases ranging from less than 1MB to several GB in size. With a solid database engine, they both perform about the same. From what I understand, MySQL scales pretty well.

[BASHERS33]

Yes as far as overall space, I see your point. But if one particular table has too many rows it sure seems like it would create problems and get very slow. I don't know. There;s not much I can do other than leave it how it is though (other than dividing the table into two tables where at least there won't be as many rows... yes it would be as much space taken up, but it would be divided into two areas).

By the way I think the rows have only 4 or 5 fields with all but one of them being mediumint or maybe int.

edit: I just asked my mom and she said on DB2 it was still pretty good eprformance on 1 million rows in a table and thatw ehn it did get to even much larger than that it was simply just more necessary to be sure the indexes and queries were as good as possible.

[WingedPanther]

I've dealt with databases with over a million records in a single table. It's not a problem with proper indexes.

[BASHERS33]

I'm curious as far as my own site... it's on a shared server and the site is very inactive. But what if I did ever get it active and the DB gets so big that it isn't allowed on shared hosting (despite them claiming there is no limit, obviously there are limits when things get slow andthey have to find someone to suspend). Is there a way to have the DB on my own computer (mysql) and still use their server as far as the file side goes? A way that's actually worth doing? Although just as if I used my computer for the server itself, it would have to always be accessible, so I guess it's not too ideal. And if I do ever get it active enough to warrant a dedicated server, I sure am not paying that amount out of pocket for a site where I get no income.

[WingedPanther]

I'd worry about that when the time comes. There are ways to get a site to support itself. Just ask Jordan :)

[BASHERS33]

Well at the rate it's going now it will be never, but I don't plan on giving up, regardless. I've tried everything people have told me to try and it hasn't worked.

[BASHERS33]

I have, but it's literally been years and it's a general discussion forum site rather than a specific one which makes it that much harder, regardless of the programs I make. Then I made a specific site and when I paid a little bit to advertise it got hits fast, but then people either joined and never posted or else just flat left. I feel like advertising isn't enough until I find some group of people who "want" to post because if guests see nobody posting then what reason do they have to post anyway? Blah. It would suck enough if I was doing it as a business and nobody showed up, but I think it's even worse that it's a hobby because I've paid money for no gain whatsoever and even paying money isn't enough for me to simply get a nice community and not even want anything back out of it moneterially.

edit: by the way, for people who think general discussion sites flat "can't" work, look how busy offtopic.com is. I haven't read anything there, but it's insanely busy. And another thing I hear is "there are already forums for everything, so why make another?". that isn't positive either. But it's definitely true that I am confused in which direction to go to make things enticing to people to be there.