 Holes in MYSQL Databases? [message #162221] |
Fri, 09 April 2010 04:17  |
The Witcher
Messages: 675
Registered: May 2009
Location: USA
Karma: 3
|
Senior Member |
|
|
Had a long discussion with a young man today who explained quite emphatically that by deleting posts and or users (even move/split and merge operations) in a forum (any forum), left "holes" in the database which would eventually make the system unstable and crash.
Off hand I am quite dubious about his claim, primarily since he did not seem able to even match my own meager knowledge about MYSQL; though I have heard such claims before I have never placed much faith in them.
As I understand it MYSQL is not all that much different than any other database in that it has tables, fields, cells, columns and rows in which data is stored, retrieved and or manipulated.
And as I understand it, running Forum Consistency, Rebuild Search Index, and Compact Messages prevents the Database from being adversely affected by the above actins!
Could someone please clarify this for me?
"I'm a Witcher, I solve human problems; not always using a sword!"
|
|
|
|
| Re: Holes in MYSQL Databases? [message #162780 is a reply to message #162221] |
Thu, 15 July 2010 11:16  |
Ernesto
Messages: 413
Registered: August 2005
Karma: 0
|
Senior Member |
|
|
The tables are auto incremental, which means that when you create a new user, that user gets the id after the last one created:
1
2
3
4
5
If you delete the user with userid 3, your table will looks like this
1
2
4
5
and when you create a new user, that user will have id 6
That is the way it HAS to be and any other design will fail horribly, because if you create a new user in "slot 3" all former references to the old user will appear in the new user, etc.
So yes, there will be "holes" but they are not harmful in any way shape or form. The only problem would occur if the "userid" field in your database is just a smallint which can only hold up to 32767 different IDs.
However, since this is 2010, noone uses tinyints in a database anymore, we use regular INT which can hold 2,147,483,647 different IDs
If the need should arise, we just increase that to a bigint which holds 9,223,372,036,854,775,807, but I doubt that you will have 2 billion posts anytime soon!
Ginnunga Gaming
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]