Re: [PHP-DB] Delete from mysql table is there a way to....
On 4/16/02 2:34 PM, "Lisi" <[EMAIL PROTECTED]> wrote: > 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 > Oh, sorry each database is different but for MySQL it's: SHOW TABLE STATUS FROM ... LIKE ... In MS SQL it's: sp_spaceused ... In Informix it's: onstat (-something I forget which but there help in onstat) I forget Oracle but it's somewhere in the Enterprise manager... Good Luck, Frank -- Frank Flynn Poet, Artist & Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Delete from mysql table is there a way to....
The tables gets fragmented as deletions, updates take place on a table. If
the DB operations get slower on a table, there is a good possibility that
the table has been fragmented.. If you are using MySQL , you can use CHECK
TABLE or myisamchk periodically to check your tables.
See: http://www.mysql.com/doc/m/y/myisamchk_syntax.html
and
http://www.mysql.com/doc/C/H/CHECK_TABLE.html
for more info..
Gurhan
-Original Message-
From: Lisi [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 5:35 PM
To: Frank Flynn; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Delete from mysql table is there a way to
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Delete from mysql table is there a way to....
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
Re: [PHP-DB] Delete from mysql table is there a way to....
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
Re: [PHP-DB] Delete from mysql table is there a way to....
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'");
> }
>
>
>
>
>
>
>
>Thanks
>Jennifer
>
>--
>The sleeper has awaken
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.344 / Virus Database: 191 - Release Date: 4/2/2002
>
>
>
>--
>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
Re: [PHP-DB] Delete from mysql table is there a way to....
On Monday 15 April 2002 08:46, Jennifer Downey wrote: > Hi all, > I'm not sure if my first post showed up so I am going to post it again and > I apologize if it shows up twice. The first post did show up. This is purely a MySQL issue. > Is there a way to delete a record from a mysql table without fragmenting > the db? No. If you do a lot of deletes then you can optimize the table now and again to ensure maximum performance. See MySQL for details. -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* There is only one way to kill capitalism -- by taxes, taxes, and more taxes. -- Karl Marx */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Delete from mysql table is there a way to....
your posts are here.
i don't think you could do this in other way.
if is possible i'm curios about it too.
Jennifer Downey a écrit :
> Hi all,
> I'm not sure if my first post showed up so I am going to post it again and I
> apologize if it shows up twice.
>
> 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'");
> }
>
> Thanks
> Jennifer
>
> --
> The sleeper has awaken
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.344 / Virus Database: 191 - Release Date: 4/2/2002
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--
Marius Ursache (3563 || 3494)
\|/ \|/
"@'/ ,. \`@"
/_| \__/ |_\
\__U_/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
