Hello,

I deleted most of the records from a table but the table size remained
about the same.

I have now "dropped" the table and recreated it and used myphpadmin to
insert a few records from a text file but still the table is about the
same size!

What is going on?!

Cheers,

Geoff

Geoff Cox wrote:
> Hello,
>
> I deleted most of the records from a table but the table size remained
> about the same.
>
> I have now "dropped" the table and recreated it and used myphpadmin to
> insert a few records from a text file but still the table is about the
> same size!
>
> What is going on?!
>
> Cheers,
>
> Geoff
>

That's normal. MySQL will not resize MyISAM table's file after dropping
rows. Rather it will attempt to reuse the empty space when you insert
new rows.

InnoDB tables will never be resized. You basically need to export
everything, delete the ibdata file and recreate everything.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Tue, 10 Jun 2008 20:59:42 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

>Geoff Cox wrote:
>> Hello,
>>
>> I deleted most of the records from a table but the table size remained
>> about the same.
>>
>> I have now "dropped" the table and recreated it and used myphpadmin to
>> insert a few records from a text file but still the table is about the
>> same size!
>>
>> What is going on?!
>>
>> Cheers,
>>
>> Geoff
>>
>
>That's normal. MySQL will not resize MyISAM table's file after dropping
>rows. Rather it will attempt to reuse the empty space when you insert
>new rows.
>
>InnoDB tables will never be resized. You basically need to export
>everything, delete the ibdata file and recreate everything.

Thanks Jerry.

Is the deleting of the ibdata file out of my reach? I am on a web
hosting server. I do have SSH access.

Cheers,

Geoff

On Tue, 10 Jun 2008 20:59:42 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

>Geoff Cox wrote:
>> Hello,
>>
>> I deleted most of the records from a table but the table size remained
>> about the same.
>>
>> I have now "dropped" the table and recreated it and used myphpadmin to
>> insert a few records from a text file but still the table is about the
>> same size!
>>
>> What is going on?!
>>
>> Cheers,
>>
>> Geoff
>>
>
>That's normal. MySQL will not resize MyISAM table's file after dropping
>rows. Rather it will attempt to reuse the empty space when you insert
>new rows.
>
>InnoDB tables will never be resized. You basically need to export
>everything, delete the ibdata file and recreate everything.

Jerry,

Something I have not appreciated. Two kinds of table?

When I use mysqldump I have this line

ENGINE=MyISAM DEFAULT CHARSET=utf8;

so that proves that I have MyISAM type of table? When I created this
table using php I didn't specify MyISAM or InnoDB.

If yes, can I do anything to reduce the size of the table?

Cheers,

Geoff

Geoff Cox <gcox@freeuk.notcom> wrote:
> <jstucklex@attglobal.net> wrote:
>>
>>MySQL will not resize MyISAM table's file after dropping
>>rows. Rather it will attempt to reuse the empty space when you insert
>>new rows.
>>
>>InnoDB tables will never be resized. You basically need to export
>>everything, delete the ibdata file and recreate everything.

> Something I have not appreciated. Two kinds of table?

Even more: http://dev.mysql.com/doc/refman/5.0/...e-engines.html
(and still more in version 6.0, check the link in the left box)

> When I use mysqldump I have this line
>
> ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> so that proves that I have MyISAM type of table? When I created this
> table using php I didn't specify MyISAM or InnoDB.

In that case MySQL will use the default storage engine, which defaults
to MyISAM.

> If yes, can I do anything to reduce the size of the table?

http://dev.mysql.com/doc/refman/5.0/...ize-table.html


XL
--
Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems

MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Geoff Cox wrote:
> On Tue, 10 Jun 2008 20:59:42 -0400, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>> Geoff Cox wrote:
>>> Hello,
>>>
>>> I deleted most of the records from a table but the table size remained
>>> about the same.
>>>
>>> I have now "dropped" the table and recreated it and used myphpadmin to
>>> insert a few records from a text file but still the table is about the
>>> same size!
>>>
>>> What is going on?!
>>>
>>> Cheers,
>>>
>>> Geoff
>>>
>> That's normal. MySQL will not resize MyISAM table's file after dropping
>> rows. Rather it will attempt to reuse the empty space when you insert
>> new rows.
>>
>> InnoDB tables will never be resized. You basically need to export
>> everything, delete the ibdata file and recreate everything.
>
> Jerry,
>
> Something I have not appreciated. Two kinds of table?
>

Two types of engines (actually, more). Each engine has its own
advantages and disadvantages - it pays to understand the pros and cons
of each when selecting an engine.

> When I use mysqldump I have this line
>
> ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> so that proves that I have MyISAM type of table? When I created this
> table using php I didn't specify MyISAM or InnoDB.
>
> If yes, can I do anything to reduce the size of the table?
>
> Cheers,
>
> Geoff
>

Yep, this is MyISAM, which is the default most shared hosting companies
use. It's easy to optimize your table; connect to the database and
issue "OPTIMIZE TABLE tablename". It can be done from a MySQL command
line, but it can also be done from any programming language where you
can issue other MySQL calls.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Wed, 11 Jun 2008 07:13:10 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:


>> ENGINE=MyISAM DEFAULT CHARSET=utf8;
>>
>> so that proves that I have MyISAM type of table? When I created this
>> table using php I didn't specify MyISAM or InnoDB.
>>
>> If yes, can I do anything to reduce the size of the table?
>>
>> Cheers,
>>
>> Geoff
>>
>
>Yep, this is MyISAM, which is the default most shared hosting companies
>use. It's easy to optimize your table; connect to the database and
>issue "OPTIMIZE TABLE tablename". It can be done from a MySQL command
>line, but it can also be done from any programming language where you
>can issue other MySQL calls.

Thanks Jerry - wil give that a go.

Cheers,

Geoff

On Wed, 11 Jun 2008 08:19:02 +0200, Axel Schwenke
<axel.schwenke@gmx.de> wrote:

>Geoff Cox <gcox@freeuk.notcom> wrote:
>> <jstucklex@attglobal.net> wrote:
>>>
>>>MySQL will not resize MyISAM table's file after dropping
>>>rows. Rather it will attempt to reuse the empty space when you insert
>>>new rows.
>>>
>>>InnoDB tables will never be resized. You basically need to export
>>>everything, delete the ibdata file and recreate everything.
>
>> Something I have not appreciated. Two kinds of table?
>
>Even more: http://dev.mysql.com/doc/refman/5.0/...e-engines.html
>(and still more in version 6.0, check the link in the left box)
>
>> When I use mysqldump I have this line
>>
>> ENGINE=MyISAM DEFAULT CHARSET=utf8;
>>
>> so that proves that I have MyISAM type of table? When I created this
>> table using php I didn't specify MyISAM or InnoDB.
>
>In that case MySQL will use the default storage engine, which defaults
>to MyISAM.
>
>> If yes, can I do anything to reduce the size of the table?
>
>http://dev.mysql.com/doc/refman/5.0/...ize-table.html

thanks for the link Axel!

Cheers

Geoff


>
>XL