Thanks for the explanation! Very clear.
How do you know how fragmented your DB is? I apologize if this is covered
somewhere, like I said I was very confused buy this whole topic.
Thanks,
-Lisi
At 01:00 PM 4/16/02 -0700, Frank Flynn wrote:
>Picture a Church, full of pews. This is your empty table. As people (your
>data) come in to the church they fill the pews but unlike real life the
>first person goes all the way to the left hand side of the first pew, the
>second sits next to them and the third... When one pew is full they start
>to fill the second pew and so on. There is no empty space.
>
>Now if you want to delete some data it's like a person leaving the Church.
>If it was the last person in fine but if you're deleting a particular row
>(as you most certainly would be) it's like a person getting up out of the
>middle of a pew and leaving.
>
>Now there is a hole there, the next person coming in will still be seated in
>the back and now you delete a few more rows (a few more people leave) and
>you have "Fragmentation" which is holes in the data as it's stored. This is
>not a problem with the data but the table takes up more space than it
>otherwise could.
>
>Q - So why doesn't the database use the empty space in the middle of the
>table?
>
>A - Sometimes it will but usually not because it would take too long to find
>it (when you want to save a new record you don't want to search through a
>large table for an empty space)
>
>This fragmentation can also happen when you update a record especially if
>you use varchar datatypes because they can change size - if you updated a
>record that had a field with the value "hi" and you set it to "hello world"
>this record would not fit in the same space it came out of. Not a problem
>for the DB but it will delete the old record and save it in a new space
>(leaving the hole).
>
>This gets more complicated if you have the data "Clustered" - that is sorted
>in a particular order. But the same idea applies when you add, update or
>delete data you wind up with blank space in your database. Sometimes you
>actually want to put empty space into your table because you know you'll be
>updating the records and they will grow - typically this is called a
>"Fillfactor" and you could set it to 70% meaning 30% of your table will be
>empty and available for use.
>
>This kind of fragmentation is unavoidable and when it becomes a performance
>problem you "reorganize" your table. Some DBMS have special commands to do
>it otherwise you can dump, truncate and reload the table.
>
>But I'll suggest that fragmentation up to 50% isn't really a big problem.
>If it is noticeable at 50% I'd suggest you're probably not using an index
>properly.
>
>Good Luck,
>Frank
>
> >
> > From: Lisi <[EMAIL PROTECTED]>
> > What exactly does it mean when a table becomes fragmented? How do you know
> > when it's happened and something should be done? All the docs and books
> > talk about how bad it is but I'm still confused on what this is and when it
> > happens.
> >
> > Thanks,
> >
> > -Lisi
> >
> >
> > At 05:00 PM 4/14/02 -0700, Jennifer Downey wrote:
> >> Hi all,
> >>
> >> Is there a way to delete a record from a mysql table without
> fragmenting the
> >> db?
> >>
> >> Here is the query I am using and it does a horrible job. Fragment city.
> >>
> >>
> >>
> ----------------------------------------------------------------------------
> >> ----
> >>
> >>
> >> if ($quantity == 0)
> >> {
> >> $gone = mysql_query("DELETE FROM {$config["prefix"]}_my_items WHERE
> >> uid={$session["uid"]} AND id = '$item'");
> >> }
> >>
> >>
> >>
> ----------------------------------------------------------------------------
>
>
>--
>Frank Flynn
>Poet, Artist & Mystic
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
