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
