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
