Martin,

use VARCHAR(255) instead of CHAR(255) to save space. Otherwise, InnoDB uses
the full 255 bytes to store the field.

There is however an exception to this: if you define any single column as
VARCHAR in the table, then MySQL silently changes all long CHAR column to
VARCHAR. These "silent column specification changes"
http://www.mysql.com/doc/S/i/Silent_column_changes.html cause surprises to
users of MySQL.

In a future release I may change the storage format of all strings so that
trailing spaces are truncated, because MySQL users are accustomed to define
CHAR(255) when they actually mean VARCHAR(255).

In InnoDB the query SELECT COUNT(*) FROM T scans the whole table counting
the rows. When the table is so big that it does not fit in the buffer pool,
it will take seconds.

Best regards,

Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB

"Martin Skoffer" wrote in message ...
>Hi,
>
>my question is about the size of the InnoDB datafiles, and I list the
>questions in the end of this mail.
>
>I am running MySQL-Max version 4.0.1 on Win2000 Prof.
>
>For test purpose I have set up a small InnoDB database allocating
>200M for each file. (innodb_data_file_path = ibdata1:200M;ibdata2:200M)
>I _thougt_ this would be more than enough for this purpose.
>
>Then I do inserts in a table like this:
>
>#
># Table structure for table 't_data'
>#
>DROP TABLE /*!32200 IF EXISTS*/ t_data;
>CREATE TABLE /*!32300 IF NOT EXISTS*/ t_data (
>  intDataPK int(10) unsigned NOT NULL auto_increment,
>  intParameterFK int(10) unsigned NOT NULL ,
>  tmsTimestamp timestamp(14) ,
>  intDataTypeFK int(10) unsigned NOT NULL ,
>  strData char(255) NOT NULL ,
>  intUserFK int(10) unsigned NOT NULL ,
>  boolReference char(1) NOT NULL DEFAULT '0' ,
>  boolApproved char(1) NOT NULL DEFAULT '0' ,
>  PRIMARY KEY (intDataPK) ,
>  INDEX parameter_index (intParameterFK) ,
>  INDEX datatype_index (intDataTypeFK) ,
>  INDEX user_index (intUserFK) ,
>  FOREIGN KEY (intUserFK) REFERENCES t_user(intUserPK) ,
>  FOREIGN KEY (intDataTypeFK) REFERENCES t_datatype(intDataTypePK) ,
>  FOREIGN KEY (intParameterFK) REFERENCES t_parameter(intParameterPK)
>) TYPE=InnoDB;
>#
>
>To test the speed of queries I do a 'select count(*) from t_data'.
>
>Now I start inserting data using a php script (bulk-inserts of 20.000 rows)
>As long as the row count of the table is below 100.000 the query runs in
>less than a second.
>When I reach 120.000 rows suddenly the query runs for 6 seconds !! And from
>here
>it is getting even worse. Every new 20.000 rows increase query-time with
>appr. 2 seconds !
>At 500.000 rows query-time equals 1min10sec.
>
>I thought this was strange, but then I discovered that my datafiles
>hit 'full' at app. 550.000 rows.
>
>From 'show table status' I get this info about the table:
>Name t_data
>Type InnoDB
>Row_format Fixed
>Rows 517626
>Avg_row_length 658 (349 removing foreign keys)
>Data_length 340639744
>Max_data_length NULL
>Index_length 29933568
>Data_free 0
>Auto_increment 681535
>Create_time NULL
>Update_time NULL
>Check_time NULL
>Create_options
>Comment InnoDB free: 3072 kB;
> (intUserFK) REFER dev/t_user(intUserPK);
> (intDataTypeFK) REFER dev/t_datatype(intDataTypePK);
> (intParameterFK) REFER dev/t_parame
>
>
>
>I have tried different datatypes and with/without foreing keys but I always
>end up
>with a huge datafile and hit the limit (200M) at appr. 550.000 rows (give
or
>take a few)
>I know it is a tight setup I am using, but I still have some questions:
>
>1. What happened when my queries suddenly began using seconds to run
instead
>af msec ?
>2. Is my setup faulty or is this 'mass-consumption' af datafile-space
normal
>?
>3. When a char(255) is defined how much storage is required to store 'test'
>? 4 or 255 bytes ?
>4. And an extra: It seems to mee, that table type 'fixed' and 'dynamic' is
>used rather
>   randomly by mySQL when setting up the database. I specified nothing an
>got a mix !!
>   Any explanation to this - and does it make a difference ??
>   Any connections between 3 and 4 ??? Yes ??
>   The manual says that ROW_FORMAT only applies to MyISAM tables. Not
InnoDB
>tables - but
>   it seems to me that it does make a great difference on InnoDB (size of
>datafiles) !!
>
>
>Thank you for any answers at all :-)
>/Martin




---------------------------------------------------------------------
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

Reply via email to