table_rows of a table

[晓磊 贾]

hi, all:

Most of time, if you want to get the records of a table, 'select
count(*) from [table_name]' is okay.
While, I find use " select TABLE_NAME, PARTITION_NAME, TABLE_ROWS,
AVG_ROW_LENGTH, DATA_LENGTH from INFORMATION_SCHEMA.PARTITIONS where
TABLE_SCHEMA = 'LN_PABB2' and TABLE_NAME='T_BOUND' ;" get a different
table_rows.
Is anyone encounter with the problem?

#NOTE: the results using different sql:


+---------------------------+----------------+------------
+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
AVG_ROW_LENGTH | DATA_LENGTH |
+---------------------------+----------------+------------
+----------------+-------------+
| T_ADMINISTRATOR | NULL | 0
| 0 | 16384 |
| T_ADMINISTRATOR_LOGIN_LOG | NULL | 0
| 0 | 16384 |
| T_ADMINISTRATOR_SOURCE | NULL | 5 |
3276 | 16384 |
| T_AREA_PRIVILEGE | NULL | 0
| 0 | 16384 |
| T_BOUND | NULL | 8182737
| 95 | 781189120 |
| T_BOUND_DATA | NULL | 16365429
| 56 | 918552576 |
| T_BOUND_EVENT | NULL | 10144037
| 86 | 880820224 |
...


mysql> select count(id) from T_BOUND_EVENT ;
+-----------+
| count(id) |
+-----------+
| 10000001 |
+-----------+
1 row in set (10.05 sec)


mysql> select count(id) from T_BOUND_EVENT ;
+-----------+
| count(id) |
+-----------+
| 10000001 |
+-----------+
1 row in set (10.05 sec)

mysql> select count(id) from T_BOUND ;
+-----------+
| count(id) |
+-----------+
| 8182546 |
+-----------+
1 row in set (6.26 sec)

mysql> select count(*) from T_BOUND ;
+----------+
| count(*) |
+----------+
| 8182546 |
+----------+
1 row in set (1.82 sec)

mysql> select count(*) from T_BOUND ;
+----------+
| count(*) |
+----------+
| 8182546 |
+----------+
1 row in set (0.00 sec)

-- Jia Xiaolei

[Lennart Jonsson]

On 2011-12-29 03:29, 晓磊 贾 wrote:
> hi, all:
>
> Most of time, if you want to get the records of a table, 'select
> count(*) from [table_name]' is okay.
> While, I find use " select TABLE_NAME, PARTITION_NAME, TABLE_ROWS,
> AVG_ROW_LENGTH, DATA_LENGTH from INFORMATION_SCHEMA.PARTITIONS where
> TABLE_SCHEMA = 'LN_PABB2' and TABLE_NAME='T_BOUND' ;" get a different
> table_rows.
> Is anyone encounter with the problem?

TABLE_ROWS is only an approximation, so you can't use it if you need
exact numbers.


/Lennart

["Álvaro G. Vicario"]

El 29/12/2011 3:29, 晓磊 贾 escribió/wrote:
> hi, all:
>
> Most of time, if you want to get the records of a table, 'select
> count(*) from [table_name]' is okay.
> While, I find use " select TABLE_NAME, PARTITION_NAME, TABLE_ROWS,
> AVG_ROW_LENGTH, DATA_LENGTH from INFORMATION_SCHEMA.PARTITIONS where
> TABLE_SCHEMA = 'LN_PABB2' and TABLE_NAME='T_BOUND' ;" get a different
> table_rows.
> Is anyone encounter with the problem?

It's actually documented. The MyISAM engine tracks the exact row count
but the InnoDB engine does not. Running a COUNT(*) SQL query is the only
exact method that works for all engines.

http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html

«Some storage engines, such as MyISAM, store the exact count. For other
storage engines, such as InnoDB, this value is an approximation, and may
vary from the actual value by as much as 40 to 50%. In such cases, use
SELECT COUNT(*) to obtain an accurate count.»


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--