Selon [EMAIL PROTECTED]: > Mathias <[EMAIL PROTECTED]> wrote on 06/28/2005 01:11:59 PM: > > > Selon [EMAIL PROTECTED]: > > > <snip> > > > > Thanks Shawn, but i'm not speaking about data consistency during > > transaction and > > isolation levels. > > I spoke about what is seen in the data dictionary as num_rows an why > > it can not > > be used even it's quite faster. > > > > > > > > Hope that helps > > :o) > > Mathias > > And I was trying to explain why there is not a number IN the data > dictionary that represents "row count". Unless a separate dictionary is > maintained FOR EACH TRANSACTION, the record counts will be wrong. The > record counts determined by SELECT COUNT(*) are *per transaction* so the > only way to do a record count is by checking each row (pending or not) > against cross-transaction isolation. > > This has everything to do with the row-level locking built into InnoDB and > unless they enhance the engine to maintain a list of table statistics (I > think this is part of what you are calling the dictionary) for each > transaction, there can't be a rapid lookup of the row count. Right now I > don't see that as high on their priorities. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > >
What i call data dictionary is this : mysql> use information_schema; mysql> select table_name,table_rows from tables; +---------------------------------------+------------+ | table_name | table_rows | +---------------------------------------+------------+ | SCHEMATA | NULL | | TABLES | NULL | | COLUMNS | NULL | | CHARACTER_SETS | NULL | | COLLATIONS | NULL | | COLLATION_CHARACTER_SET_APPLICABILITY | NULL | | ROUTINES | NULL | | STATISTICS | NULL | | VIEWS | NULL | | USER_PRIVILEGES | NULL | | SCHEMA_PRIVILEGES | NULL | | TABLE_PRIVILEGES | NULL | | COLUMN_PRIVILEGES | NULL | | TABLE_CONSTRAINTS | NULL | | KEY_COLUMN_USAGE | NULL | | columns_priv | 0 | | db | 0 | | func | 0 | | help_category | 29 | | help_keyword | 325 | | help_relation | 548 | | help_topic | 405 | | host | 0 | | proc | 0 | | procs_priv | 0 | | tables_priv | 0 | | time_zone | 0 | | time_zone_leap_second | 0 | | time_zone_name | 0 | | time_zone_transition | 0 | | time_zone_transition_type | 0 | | user | 1 | +---------------------------------------+------------+ 32 rows in set (0.06 sec) mysql> create table test.test1(a int); Query OK, 0 rows affected (0.08 sec) mysql> insert into test.test1 values(1); Query OK, 1 row affected (0.01 sec) mysql> insert into test.test1 values(2); Query OK, 1 row affected (0.01 sec) mysql> select table_name,table_rows from tables; +---------------------------------------+------------+ | table_name | table_rows | +---------------------------------------+------------+ | SCHEMATA | NULL | | TABLES | NULL | | COLUMNS | NULL | | CHARACTER_SETS | NULL | | COLLATIONS | NULL | | COLLATION_CHARACTER_SET_APPLICABILITY | NULL | | ROUTINES | NULL | | STATISTICS | NULL | | VIEWS | NULL | | USER_PRIVILEGES | NULL | | SCHEMA_PRIVILEGES | NULL | | TABLE_PRIVILEGES | NULL | | COLUMN_PRIVILEGES | NULL | | TABLE_CONSTRAINTS | NULL | | KEY_COLUMN_USAGE | NULL | | columns_priv | 0 | | db | 0 | | func | 0 | | help_category | 29 | | help_keyword | 325 | | help_relation | 548 | | help_topic | 405 | | host | 0 | | proc | 0 | | procs_priv | 0 | | tables_priv | 0 | | time_zone | 0 | | time_zone_leap_second | 0 | | time_zone_name | 0 | | time_zone_transition | 0 | | time_zone_transition_type | 0 | | user | 1 | | test1 | 2 | <===== COUNT(*) EXITS +---------------------------------------+------------+ 33 rows in set (0.06 sec) mysql> show create table test.test1; +-------+----------------------------------------------------- | Table | Create Table +-------+----------------------------------------------------- | test1 | CREATE TABLE `test1` ( `a` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------- 1 row in set (0.00 sec) This is just my proposition clarification. But it works in v5.x Thanks. Hope that helps :o) Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
