This is a forum where members new and old can come to ask questions and get info and opinions. It is not a place to advertise your business or have other forms of advertising, whether it be in your posts or signature.

All links in the forum will not be indexed by Search Engines and any unapproved forms of advertising or spam will be dealt with accordingly, deleted, and that member's account banned.

Forums

Overview » SQL and Databases » Fisically Remove rows in MYSQL

Fisically Remove rows in MYSQL
kandir pare Member Since: 20 Oct 2009 Posts: 1	Posted 20 Oct 2009 07:58:21 HI guys I need to know how to fisically remove rows from a table, I'm working with a MySQL 4.1 database. Cause I've noticed that after having deleted too many rows from a very large table (with a SQL statement, ex. DELETE FROM tablename), the size (in GB's) of the database keep being the same , and so the performance of the queries I run on that table. I need to know if there's any command or tool to pack a table or work with it's rows (deleted or not) The table type I use is InnoDB. HI guys I need to know how to fisically remove rows from a table, I'm working with a MySQL 4.1 database. Cause I've noticed that after having deleted too many rows from a very large table (with a SQL statement, ex. DELETE FROM tablename), the size (in GB's) of the database keep being the same , and so the performance of the queries I run on that table. I need to know if there's any command or tool to pack a table or work with it's rows (deleted or not) The table type I use is InnoDB.

Fisically Remove rows in MYSQL

kandir pare
Member

Since: 20 Oct 2009
Posts: 1

Posted 20 Oct 2009 07:58:21

HI guys

I need to know how to fisically remove rows from a table, I'm working with a MySQL 4.1 database.
Cause I've noticed that after having deleted too many rows from a very large table (with a SQL statement, ex. DELETE FROM tablename),
the size (in GB's) of the database keep being the same , and so the performance of the queries I run on that table.

I need to know if there's any command or tool to pack a table or work with it's rows (deleted or not)
The table type I use is InnoDB.

HI guys

I need to know how to fisically remove rows from a table, I'm working with a MySQL 4.1 database.
Cause I've noticed that after having deleted too many rows from a very large table (with a SQL statement, ex. DELETE FROM tablename),
the size (in GB's) of the database keep being the same , and so the performance of the queries I run on that table.

I need to know if there's any command or tool to pack a table or work with it's rows (deleted or not)
The table type I use is InnoDB.

Patrick Julicher
Official Representative

Since: 04 Feb 2002
Posts: 1,208

Replied 20 Oct 2009 08:09:03

Hi,

Please check this out:

What I think throws some people off is that when this happens, records that are "deleted" are not actually removed from disk. The database knows those records are not to be used anymore, but the disk space is not deallocated, it's just considered "dead" space. This is perfectly harmless. When new records are added to the database, they get written to this "dead" space, so no new disk space needs to be allocated for them. In other words, the space taken up by the "deleted" records is reused by the database to store new records.

The reason most database applications don't physically delete records at the time they're "deleted" is simple--efficiency. The database itself is typically a huge file on disk, and if you want to delete part of it you have to interact with the filesystem to write a new (modified) copy of it and then delete the old one. This takes time and system resources, and your database application would slow to a crawl if it had to do that every time a record was deleted.

If you still want to loose the dead space, there should be an optimize/vacuum option you could use.

Kind regards, Patrick

Reply to this topic

Message
Reply

Live Support

Visit All Zones

Latest Extensions

Premium Articles

DMXzone Updater

Our Sponsors

Find Us on Facebook

Featured Extension

Extensions Yearly Subscription

Tired of purchasing many extensions just to finish up your project. Now we have a solution for you. For a small fee you have access to over 70 high-quality (all DMXzone extensions) and growing Dreamweaver extensions for a year period!

Additionally, we're working on many new ones, which will be available for every subscription holder when released!