Re: Avg_row_length

2003-01-17 Thread Roger Baklund 
* Stefan Hinz
> * Sergei:
> > It's because of NULLs.
> > Extra byte is used to mark, if the column is NULL or not.
>
> Thanks alot for the explanation. Thanks to Gerald Clark, too, who told
> me the same.
>
> > (in fact it's a bitmap, that is it would be one byte for up to 8
> > columns, then it'll be 2 bytes, etc.)
>
> If so, this table should have an average row length of 92, not 91,
> shouldn't it (9 cols * 10 bytes + 2 bytes)?
>
> MySQL>DESCRIBE myrowisam;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | col1  | char(10) |  | | |   |
> | col2  | char(10) |  | | |   |
> | col3  | char(10) |  | | |   |
> | col4  | char(10) |  | | |   |
> | col5  | char(10) |  | | |   |
> | col6  | char(10) |  | | |   |
> | col7  | char(10) |  | | |   |
> | col8  | char(10) |  | | |   |
> | col9  | char(10) |  | | |   |
> +---+--+--+-+-+---+

These columns are all defined as NOT NULL, the mentioned bitmap does not
apply. The extra byte you see is a deletion flag, used internaly when you
delete a record from a fixed width table. The unused 7 bits of this byte is
used for the bitmap when you use NULLs, allowing up to 15 NULL fields to be
stored with only one additional byte per record: 15 char(10) fields would
give a Avg_row_length of 152, while 16 fields would give a Avg_row_length of
163. If there are fewer than 8 NULL fields, no additional byte is needed for
the bitmap, leaving the Avg_row_length at the combined field length + 1 for
the deletion flag.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-17 Thread Stefan Hinz 
Gerald,

> >>(in fact it's a bitmap, that is it would be one byte for up to 8
> >>columns, then it'll be 2 bytes, etc.)

> >If so, this table should have an average row length of 92, not 91,

> It does on mine.

Alright, I take this as a Windows goodie (my MySQL 4.0.7 runs on Win2K
;-)

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Stefan Hinz" <[EMAIL PROTECTED]>
Cc: "Sergei Golubchik" <[EMAIL PROTECTED]>; "Keith C. Ivey"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, January 17, 2003 3:48 PM
Subject: Re: Avg_row_length


>
>
> Stefan Hinz wrote:
>
> >Sergei,
> >
> >
> >
> >>>any other guru who can explain why 10 = 11?
> >>>
> >>>
> >
> >
> >
> >>It's because of NULLs.
> >>Extra byte is used to mark, if the column is NULL or not.
> >>
> >>
> >
> >Thanks alot for the explanation. Thanks to Gerald Clark, too, who
told
> >me the same.
> >
> >
> >
> >>(in fact it's a bitmap, that is it would be one byte for up to 8
> >>columns, then it'll be 2 bytes, etc.)
> >>
> >>
> >
> >If so, this table should have an average row length of 92, not 91,
> >
>
> It does on mine.
>
> >shouldn't it (9 cols * 10 bytes + 2 bytes)?
> >
> >MySQL>DESCRIBE myrowisam;
> >+---+--+--+-+-+---+
> >| Field | Type | Null | Key | Default | Extra |
> >+---+--+--+-+-+---+
> >| col1  | char(10) |  | | |   |
> >| col2  | char(10) |  | | |   |
> >| col3  | char(10) |  | | |   |
> >| col4  | char(10) |  | | |   |
> >| col5  | char(10) |  | | |   |
> >| col6  | char(10) |  | | |   |
> >| col7  | char(10) |  | | |   |
> >| col8  | char(10) |  | | |   |
> >| col9  | char(10) |  | | |   |
> >+---+--+--+-+-+---+
> >MySQL>SELECT * FROM myrowisam;
> >+--+--+--+--+--+--+--+--+--+
> >| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 |
> >+--+--+--+--+--+--+--+--+--+
> >| foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  |
> >+--+--+--+--+--+--+--+--+--+
> >MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
> >+---+++--++
> >| Name  | Type   | Row_format | Rows | Avg_row_length |
> >+---+++--++
> >| myrowisam | MyISAM | Fixed  |    1 |     91 |
> >+---+++--++
> >
> >Regards,
> >--
> >  Stefan Hinz <[EMAIL PROTECTED]>
> >  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
> >  Heesestr. 6, 12169 Berlin (Germany)
> >  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
> >
> >- Original Message -
> >From: "Sergei Golubchik" <[EMAIL PROTECTED]>
> >To: "Stefan Hinz" <[EMAIL PROTECTED]>
> >Cc: "Keith C. Ivey" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> >Sent: Thursday, January 16, 2003 9:16 PM
> >Subject: Re: Avg_row_length
> >
> >
> >
> >
> >>Hi!
> >>
> >>On Jan 16, Stefan Hinz wrote:
> >>
> >>
> >>>Keith,
> >>>
> >>>thanks once again! Well, anyone here from the MySQL developer team?
> >>>
> >>>
> >Or
> >
> >
> >>>any other guru who can explain why 10 = 11?
> >>>
> >>>Here's the problem once again:
> >>>
> >>>mysql> DESCRIBE myrowisam;
> >>>+---+--+--+-+-+---+
> >>>| Field | Type | Null | Key | Default | Extra |
> >>>+---+--+--+-+-+---+
> >>>| col   | char(10) | YES  | MUL | NULL|   |
> >>>+---+--+--+-+-+---+
> >>>mysql> SHOW TABLE STATUS LIKE 'myrowisam';
&g

Re: Avg_row_length

2003-01-17 Thread Sergei Golubchik 
Hi!

On Jan 16, Stefan Hinz wrote:
> Sergei,
> 
> >> any other guru who can explain why 10 = 11?
> 
> > It's because of NULLs.
> > Extra byte is used to mark, if the column is NULL or not.
> 
> Thanks alot for the explanation. Thanks to Gerald Clark, too, who told
> me the same.
> 
> > (in fact it's a bitmap, that is it would be one byte for up to 8
> > columns, then it'll be 2 bytes, etc.)
> 
> If so, this table should have an average row length of 92, not 91,
> shouldn't it (9 cols * 10 bytes + 2 bytes)?

No, it shouldn't :)

All the columns are NOT NULL, so there's no need for a is_null-bitmap.
Extra byte is still necessary, as a 'row is deleted' marker.
 
> MySQL>DESCRIBE myrowisam;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | col1  | char(10) |  | | |   |
> | col2  | char(10) |  | | |   |
> | col3  | char(10) |  | | |   |
> | col4  | char(10) |  | | |   |
> | col5  | char(10) |  | | |   |
> | col6  | char(10) |  | | |   |
> | col7  | char(10) |  | | |   |
> | col8  | char(10) |  | | |   |
> | col9  | char(10) |  | | |   |
> +---+--+--+-+-+---+
> MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
> +---+++--++
> | Name  | Type   | Row_format | Rows | Avg_row_length |
> +---+++--++
> | myrowisam | MyISAM | Fixed  |1 | 91 |
> +---+++--++
> 
> Regards,
> --
>   Stefan Hinz <[EMAIL PROTECTED]>
>   Gesch?ftsf?hrer / CEO iConnect GmbH 
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-17 Thread gerald_clark 


Stefan Hinz wrote:


Sergei,

 

any other guru who can explain why 10 = 11?
 


 

It's because of NULLs.
Extra byte is used to mark, if the column is NULL or not.
   


Thanks alot for the explanation. Thanks to Gerald Clark, too, who told
me the same.

 

(in fact it's a bitmap, that is it would be one byte for up to 8
columns, then it'll be 2 bytes, etc.)
   


If so, this table should have an average row length of 92, not 91,



It does on mine.


shouldn't it (9 cols * 10 bytes + 2 bytes)?

MySQL>DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col1  | char(10) |  | | |   |
| col2  | char(10) |  | | |   |
| col3  | char(10) |  | | |   |
| col4  | char(10) |  | | |   |
| col5  | char(10) |  | | |   |
| col6  | char(10) |  | | |   |
| col7  | char(10) |  | | |   |
| col8  | char(10) |  | | |   |
| col9  | char(10) |  | | |   |
+---+--+--+-+-+---+
MySQL>SELECT * FROM myrowisam;
+--+--+--+--+--+--+--+--+--+
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 |
+--+--+--+--+--+--+--+--+--+
| foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  |
+--+--+--+--+--+--+--+--+--+
MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name  | Type   | Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed  |1 | 91 |
+---+++--++

Regards,
--
 Stefan Hinz <[EMAIL PROTECTED]>
 Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
 Heesestr. 6, 12169 Berlin (Germany)
 Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Sergei Golubchik" <[EMAIL PROTECTED]>
To: "Stefan Hinz" <[EMAIL PROTECTED]>
Cc: "Keith C. Ivey" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 16, 2003 9:16 PM
Subject: Re: Avg_row_length


 

Hi!

On Jan 16, Stefan Hinz wrote:
   

Keith,

thanks once again! Well, anyone here from the MySQL developer team?
 

Or
 

any other guru who can explain why 10 = 11?

Here's the problem once again:

mysql> DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) | YES  | MUL | NULL|   |
+---+--+--+-+-+---+
mysql> SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name  | Type   | Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed  |  109 | 11 |
+---+++--++

Regards,
--
 Stefan Hinz <[EMAIL PROTECTED]>
 Gesch?ftsf?hrer / CEO iConnect GmbH <http://iConnect.de>
 Heesestr. 6, 12169 Berlin (Germany)
 Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
 

It's because of NULLs.
Extra byte is used to mark, if the column is NULL or not.

(in fact it's a bitmap, that is it would be one byte for up to 8
columns, then it'll be 2 bytes, etc.)

Regards,
Sergei

--
MySQL Development Team
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
  <___/
   




 




-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-16 Thread Stefan Hinz 
Sergei,

>> any other guru who can explain why 10 = 11?

> It's because of NULLs.
> Extra byte is used to mark, if the column is NULL or not.

Thanks alot for the explanation. Thanks to Gerald Clark, too, who told
me the same.

> (in fact it's a bitmap, that is it would be one byte for up to 8
> columns, then it'll be 2 bytes, etc.)

If so, this table should have an average row length of 92, not 91,
shouldn't it (9 cols * 10 bytes + 2 bytes)?

MySQL>DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col1  | char(10) |  | | |   |
| col2  | char(10) |  | | |   |
| col3  | char(10) |  | | |   |
| col4  | char(10) |  | | |   |
| col5  | char(10) |  | | |   |
| col6  | char(10) |  | | |   |
| col7  | char(10) |  | | |   |
| col8  | char(10) |  | | |   |
| col9  | char(10) |  | | |   |
+---+--+--+-+-+---+
MySQL>SELECT * FROM myrowisam;
+--+--+--+--+--+--+--+--+--+
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 |
+--+--+--+--+--+--+--+--+--+
| foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  | foo  |
+--+--+--+--+--+--+--+--+--+
MySQL>SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name  | Type   | Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed  |1 | 91 |
+---+++--++

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Sergei Golubchik" <[EMAIL PROTECTED]>
To: "Stefan Hinz" <[EMAIL PROTECTED]>
Cc: "Keith C. Ivey" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 16, 2003 9:16 PM
Subject: Re: Avg_row_length


> Hi!
>
> On Jan 16, Stefan Hinz wrote:
> > Keith,
> >
> > thanks once again! Well, anyone here from the MySQL developer team?
Or
> > any other guru who can explain why 10 = 11?
> >
> > Here's the problem once again:
> >
> > mysql> DESCRIBE myrowisam;
> > +---+--+--+-+-+---+
> > | Field | Type | Null | Key | Default | Extra |
> > +---+--+--+-+-+---+
> > | col   | char(10) | YES  | MUL | NULL|   |
> > +---+--+--+-+-+---+
> > mysql> SHOW TABLE STATUS LIKE 'myrowisam';
> > +---+++--++
> > | Name  | Type   | Row_format | Rows | Avg_row_length |
> > +---+++--++
> > | myrowisam | MyISAM | Fixed  |  109 | 11 |
> > +---+++--++
> >
> > Regards,
> > --
> >   Stefan Hinz <[EMAIL PROTECTED]>
> >   Gesch?ftsf?hrer / CEO iConnect GmbH <http://iConnect.de>
> >   Heesestr. 6, 12169 Berlin (Germany)
> >   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
>
> It's because of NULLs.
> Extra byte is used to mark, if the column is NULL or not.
>
> (in fact it's a bitmap, that is it would be one byte for up to 8
> columns, then it'll be 2 bytes, etc.)
>
> Regards,
> Sergei
>
> --
> MySQL Development Team
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
>  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
> /_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
><___/


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-16 Thread Zak Greant 
On Thu, Jan 16, 2003 at 06:47:03PM +0100, Stefan Hinz wrote:
> Keith,
> 
> thanks once again! Well, anyone here from the MySQL developer team? Or
> any other guru who can explain why 10 = 11?

  Hi Stefan,

  Here is my guess. ;)

  CHAR columns are fixed width. A CHAR column with a width of 10 will
  always require 10 bytes - regardless of what is put into it.

  If the column also allows NULL values, then the length of all columns 
  becomes 10 bytes + 1 byte for the NULL. 

  Cheers!
-- 
 Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate |  http://zak.fooassociates.com

Developing Dynamic Web Applications with MySQL and PHP
  MySQL Training: Nuernburg, June 02-06, 2003
  Visit http://mysql.com/training for more information

"Gosh, Batman. The nobility of the almost-human porpoise."
  --Robin, the Boy Wonder

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-16 Thread Sergei Golubchik 
Hi!

On Jan 16, Stefan Hinz wrote:
> Keith,
> 
> thanks once again! Well, anyone here from the MySQL developer team? Or
> any other guru who can explain why 10 = 11?
> 
> Here's the problem once again:
> 
> mysql> DESCRIBE myrowisam;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | col   | char(10) | YES  | MUL | NULL|   |
> +---+--+--+-+-+---+
> mysql> SHOW TABLE STATUS LIKE 'myrowisam';
> +---+++--++
> | Name  | Type   | Row_format | Rows | Avg_row_length |
> +---+++--++
> | myrowisam | MyISAM | Fixed  |  109 | 11 |
> +---+++--++
> 
> Regards,
> --
>   Stefan Hinz <[EMAIL PROTECTED]>
>   Gesch?ftsf?hrer / CEO iConnect GmbH 
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

It's because of NULLs.
Extra byte is used to mark, if the column is NULL or not.

(in fact it's a bitmap, that is it would be one byte for up to 8
columns, then it'll be 2 bytes, etc.)

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-16 Thread gerald_clark 
It looks to me like 1 bit for deleted, and 1 bit for each column to 
store NULL.
1 byte for 1-7 fields.
2 bytes for 8-15 fields.

Stefan Hinz wrote:

Keith,

thanks once again! Well, anyone here from the MySQL developer team? Or
any other guru who can explain why 10 = 11?

Here's the problem once again:

mysql> DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) | YES  | MUL | NULL|   |
+---+--+--+-+-+---+
mysql> SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name  | Type   | Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed  |  109 | 11 |
+---+++--++

Regards,
--
 Stefan Hinz <[EMAIL PROTECTED]>
 Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
 Heesestr. 6, 12169 Berlin (Germany)
 Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Keith C. Ivey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Stefan Hinz" <[EMAIL PROTECTED]>
Sent: Thursday, January 16, 2003 5:05 PM
Subject: Re: Avg_row_length


 

On 16 Jan 2003, at 0:37, Stefan Hinz wrote:

   

There's a formula to calculate the row length for dynamic MyISAM
tables here:
  http://www.mysql.com/doc/en/Dynamic_format.html
   

Thanks for the hint, but this doesn't explain fixed length row
calculations where char(10) will give an average row length of 11
(bytes), or does it?
 

You're right, it doesn't.  I forgot that you were talking about fixed-
length records.  I'm afraid I don't have anything else to add then,
since I know nothing about the storage format other than what I've
seen in the documentation.

[Filter fodder: SQL]

--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653
   



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


 




-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-16 Thread Stefan Hinz 
Keith,

thanks once again! Well, anyone here from the MySQL developer team? Or
any other guru who can explain why 10 = 11?

Here's the problem once again:

mysql> DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) | YES  | MUL | NULL|   |
+---+--+--+-+-+---+
mysql> SHOW TABLE STATUS LIKE 'myrowisam';
+---+++--++
| Name  | Type   | Row_format | Rows | Avg_row_length |
+---+++--++
| myrowisam | MyISAM | Fixed  |  109 | 11 |
+---+++--++

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Keith C. Ivey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Stefan Hinz" <[EMAIL PROTECTED]>
Sent: Thursday, January 16, 2003 5:05 PM
Subject: Re: Avg_row_length


> On 16 Jan 2003, at 0:37, Stefan Hinz wrote:
>
> > > There's a formula to calculate the row length for dynamic MyISAM
> > > tables here:
> > >http://www.mysql.com/doc/en/Dynamic_format.html
> >
> > Thanks for the hint, but this doesn't explain fixed length row
> > calculations where char(10) will give an average row length of 11
> > (bytes), or does it?
>
> You're right, it doesn't.  I forgot that you were talking about fixed-
> length records.  I'm afraid I don't have anything else to add then,
> since I know nothing about the storage format other than what I've
> seen in the documentation.
>
> [Filter fodder: SQL]
>
> --
> Keith C. Ivey <[EMAIL PROTECTED]>
> Tobacco Documents Online
> http://tobaccodocuments.org
> Phone 202-667-6653


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-16 Thread Keith C. Ivey 
On 16 Jan 2003, at 0:37, Stefan Hinz wrote:

> > There's a formula to calculate the row length for dynamic MyISAM
> > tables here:
> >http://www.mysql.com/doc/en/Dynamic_format.html
> 
> Thanks for the hint, but this doesn't explain fixed length row
> calculations where char(10) will give an average row length of 11
> (bytes), or does it?

You're right, it doesn't.  I forgot that you were talking about fixed-
length records.  I'm afraid I don't have anything else to add then, 
since I know nothing about the storage format other than what I've 
seen in the documentation.

[Filter fodder: SQL]

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-15 Thread Stefan Hinz 
Keith,

> There's a formula to calculate the row length for dynamic MyISAM
> tables here:
>http://www.mysql.com/doc/en/Dynamic_format.html

Thanks for the hint, but this doesn't explain fixed length row
calculations where char(10) will give an average row length of 11
(bytes), or does it?

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Keith C. Ivey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Stefan Hinz" <[EMAIL PROTECTED]>
Sent: Wednesday, January 15, 2003 4:39 PM
Subject: Re: Avg_row_length


> On 15 Jan 2003, at 13:03, Stefan Hinz wrote:
>
> > > I believe there is an extra byte for the 'null' flag; if you
declare
> > > this column to be NOT NULL, I'll bet the avg length will be 10.
> >
> > nice bet, but you lose :
>
> In addition to the bitmap for which columns have null values (if
> there are any columns that can be null), there's a similar bitmap to
> show which columns have zero values (or the empty string, for string
> columns).  That normally saves space because no additional storage is
> used if the column is zero.
>
> There's a formula to calculate the row length for dynamic MyISAM
> tables here:
>
>http://www.mysql.com/doc/en/Dynamic_format.html
>
> --
> Keith C. Ivey <[EMAIL PROTECTED]>
> Tobacco Documents Online
> http://tobaccodocuments.org
> Phone 202-667-6653


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-15 Thread Keith C. Ivey 
On 15 Jan 2003, at 13:03, Stefan Hinz wrote:

> > I believe there is an extra byte for the 'null' flag; if you declare
> > this column to be NOT NULL, I'll bet the avg length will be 10.
> 
> nice bet, but you lose :

In addition to the bitmap for which columns have null values (if 
there are any columns that can be null), there's a similar bitmap to 
show which columns have zero values (or the empty string, for string 
columns).  That normally saves space because no additional storage is 
used if the column is zero.

There's a formula to calculate the row length for dynamic MyISAM 
tables here:

   http://www.mysql.com/doc/en/Dynamic_format.html

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-15 Thread Stefan Hinz 
Steve,

> mysql> create table t2 (c1 varchar(255));
> Query OK, 0 rows affected (0.00 sec)
> mysql> insert into t2 values ('s');
> Query OK, 1 row affected (0.00 sec)

> And this gave table status of
> Name: t2
> Type: MyISAM
>   Row_format: Dynamic
> Rows: 1
>   Avg_row_length: 20

Well, isn't the row length supposed to be 2 (not 20)? I mean 1 byte for
the character 's', plus 1 byte to store the length.

BTW, the manual doesn't say anything about "internal uses" of additional
bits/bytes, AFAIK. Thanks anyway.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Steve Edberg" <[EMAIL PROTECTED]>
To: "Stefan Hinz" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, January 15, 2003 2:18 PM
Subject: Re: Avg_row_length


> Yep, you're right!
>
> Perhaps an extra byte is automatically reserved, not only for a
> null/not null status bit, but also for other internal use - eg; a
> 'row changed' bit, etc.
>
> I did a quick test:
>
> mysql> create table t2 (c1 varchar(255));
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into t2 values ('s');
> Query OK, 1 row affected (0.00 sec)
>
> And this gave table status of
>
> Name: t2
> Type: MyISAM
>   Row_format: Dynamic
> Rows: 1
>   Avg_row_length: 20
>  Data_length: 20
> Max_data_length: 4294967295
> Index_length: 1024
>Data_free: 0
>   Auto_increment: NULL
>
> so obviously the avg_row_length includes extra bytes for mysql
internal info.
>
> Learn something every day!
>
> -steve
>
>
> At 1:03 PM +0100 1/15/03, you wrote:
> >Steve,
> >
> >>  I believe there is an extra byte for the 'null' flag; if you
declare
> >>  this column to be NOT NULL, I'll bet the avg length will be 10.
> >
> >nice bet, but you lose :
> >
> >mysql> DESCRIBE mynotnullisam;
> >+---+--+--+-+-+---+
> >| Field | Type | Null | Key | Default | Extra |
> >+---+--+--+-+-+---+
> >| col   | char(10) |  | | |   |
> >+---+--+--+-+-+---+
> >1 row in set (0.16 sec)
> >
> >mysql> SHOW TABLE STATUS LIKE 'mynotnullisam';
>
>+---+++--++
-
> >+
> >| Name  | Type   | Row_format | Rows | Avg_row_length |
> >Data_length |
>
>+---+++--++
-
> >+
> >| mynotnullisam | MyISAM | Fixed  |3 | 11 |
> >33 |
>
>+---+++--++
-
> >+
> >
>
>
> --
>
+---
-+
> | Steve Edberg
[EMAIL PROTECTED] |
> | University of California, Davis
(530)754-9127 |
> | Programming/Database/SysAdmin
http://pgfsun.ucdavis.edu/ |
>
+---
-+
> | SETI@Home: 1001 Work units on 23 oct 2002
|
> | 3.152 years CPU time, 3.142 years SETI user... and STILL no
aliens...  |
>
+---
-+


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-15 Thread Steve Edberg 
Yep, you're right!

Perhaps an extra byte is automatically reserved, not only for a 
null/not null status bit, but also for other internal use - eg; a 
'row changed' bit, etc.

I did a quick test:

mysql> create table t2 (c1 varchar(255));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values ('s');
Query OK, 1 row affected (0.00 sec)

And this gave table status of

   Name: t2
   Type: MyISAM
 Row_format: Dynamic
   Rows: 1
 Avg_row_length: 20
Data_length: 20
Max_data_length: 4294967295
   Index_length: 1024
  Data_free: 0
 Auto_increment: NULL

so obviously the avg_row_length includes extra bytes for mysql internal info.

Learn something every day!

-steve


At 1:03 PM +0100 1/15/03, you wrote:
Steve,


 I believe there is an extra byte for the 'null' flag; if you declare
 this column to be NOT NULL, I'll bet the avg length will be 10.


nice bet, but you lose :

mysql> DESCRIBE mynotnullisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) |  | | |   |
+---+--+--+-+-+---+
1 row in set (0.16 sec)

mysql> SHOW TABLE STATUS LIKE 'mynotnullisam';
+---+++--++-
+
| Name  | Type   | Row_format | Rows | Avg_row_length |
Data_length |
+---+++--++-
+
| mynotnullisam | MyISAM | Fixed  |3 | 11 |
33 |
+---+++--++-
+




--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-14 Thread Dan Nelson 
In the last episode (Jan 14), Stefan Hinz said:
> here's my final stupid question for today. Why is the average row
> length 11 when it should be 10?
> 
> mysql> DESCRIBE myrowisam;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | col   | char(10) | YES  | | NULL|   |
> +---+--+--+-+-+---+
> 
> mysql> SHOW TABLE STATUS LIKE 'myrow%';
> +-+++--++
> | Name| Type   | Row_format | Rows | Avg_row_length |
> +-+++--++
> | myrowisam   | MyISAM | Fixed  |   68 | 11 | <---

 * Each `NULL' column takes one bit extra, rounded up to the nearest
   byte.


-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Avg_row_length

2003-01-14 Thread Steve Edberg 
I believe there is an extra byte for the 'null' flag; if you declare
this column to be NOT NULL, I'll bet the avg length will be 10.

-steve

At 10:43 PM +0100 1/14/03, Stefan Hinz wrote:

Dear gurus,

here's my final stupid question for today. Why is the average row length
11 when it should be 10?

mysql> DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) | YES  | | NULL|   |
+---+--+--+-+-+---+

mysql> SHOW TABLE STATUS LIKE 'myrow%';
+-+++--++
| Name| Type   | Row_format | Rows | Avg_row_length |
+-+++--++
| myrowisam   | MyISAM | Fixed  |   68 | 11 | <---

TIA,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3





--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Avg_row_length

2003-01-14 Thread Jennifer Goodie 
I am not a guru, but I figured I'd try to answer anyway.  It needs an extra
bit because it has NULLs.  I think it uses the bit to mark if the field is
NULL or not, but I've been known to be wrong.

http://www.mysql.com/doc/en/Data_size.html



-
Dear gurus,

here's my final stupid question for today. Why is the average row length
11 when it should be 10?

mysql> DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) | YES  | | NULL|   |
+---+--+--+-+-+---+

mysql> SHOW TABLE STATUS LIKE 'myrow%';
+-+++--++
| Name| Type   | Row_format | Rows | Avg_row_length |
+-+++--++
| myrowisam   | MyISAM | Fixed  |   68 | 11 | <---

TIA,


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php