Hi all. I have two questions about InnoDB table type.
1) I execute command SHOW TABLE STATUS for a table and the value for column Rows is different from the result of SELECT COUNT(*) for that table. mysql> SHOW TABLE STATUS LIKE 'ALBARANCL'; +-----------+--------+------------+--------+----------------+-------------+- ----------------+--------------+-----------+----------------+-------------+- ------------+------------+----------------+-------------------------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +-----------+--------+------------+--------+----------------+-------------+- ----------------+--------------+-----------+----------------+-------------+- ------------+------------+----------------+-------------------------+ | ALBARANCL | InnoDB | Dynamic | 862226 | 460 | 397410304 | NULL | 282361856 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 1013760 kB | +-----------+--------+------------+--------+----------------+-------------+- ----------------+--------------+-----------+----------------+-------------+- ------------+------------+----------------+-------------------------+ 1 row in set (0.54 sec) mysql> SELECT COUNT(*) FROM ALBARANCL; +----------+ | COUNT(*) | +----------+ | 1042512 | +----------+ 1 row in set (1 min 23.64 sec) The table is of type InnoDB. I'm testing InnoDB against MyISAM, and I have the same data in another database with MyISAM tables. In that database the result from SELECT COUNT(*) is the same that the value of column Rows in table STATUS. The sames commands in the MyISAM database: mysql> SHOW TABLE STATUS LIKE 'ALBARANCL'; +-----------+--------+------------+---------+----------------+-------------+ -----------------+--------------+-----------+----------------+-------------- -------+---------------------+------------+----------------+---------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +-----------+--------+------------+---------+----------------+-------------+ -----------------+--------------+-----------+----------------+-------------- -------+---------------------+------------+----------------+---------+ | ALBARANCL | MyISAM | Dynamic | 1042512 | 156 | 163191480 | 4294967295 | 70662144 | 0 | NULL | 2002-05-30 13:46:42 | 2002-05-30 13:57:40 | NULL | | | +-----------+--------+------------+---------+----------------+-------------+ -----------------+--------------+-----------+----------------+-------------- -------+---------------------+------------+----------------+---------+ 1 row in set (0.01 sec) mysql> SELECT COUNT(*) FROM ALBARANCL; +----------+ | COUNT(*) | +----------+ | 1042512 | +----------+ 1 row in set (0.00 sec) 2) Is is normal the difference in time for executing these commands between InnoDB and MyISAM tables. The data is the same. Could I have a wrong InnoDB configuration? I read carefully section InnoDB in MySQL manual and set the innodb parameters as: # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M innodb_data_home_dir = /mysql/ innodb_log_group_home_dir = /mysql/ innodb_log_arch_dir = /mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=350M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Note: I use my-huge.cnf and the machine is a 2 Pentium III 1000GHz, Memory 1G, 30GB SCSSI. I'm using FreeBSD 4.5. Is there any guide for configuring InnoDB with more detailed examples? Thanks in advance. Iago. --------------------------------------------------------------------- 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
