How to see table partition size in MySQL ( is it even possible? )

Question

I've partitioned my table horizontally and I'd like to see how the rows are currently distributed. Searching the web didn't bring any relevant results.

Could anyone tell me if this is possible?

Answer 1

You could get rows of each partitions using information_schema.

Here are my sample tests.

mysql> SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD
       FROM information_schema.PARTITIONS 
       WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name';
+----------------------------+------------+------------------+
| PARTITION_ORDINAL_POSITION | TABLE_ROWS | PARTITION_METHOD |
+----------------------------+------------+------------------+
|                          1 |          2 | HASH             |
|                          2 |          3 | HASH             |
+----------------------------+------------+------------------+

mysql> SHOW CREATE TABLE tbl_name\G
*************************** 1. row ***************************
       Table: p
Create Table: CREATE TABLE `tbl_name` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (a)
PARTITIONS 2 */
1 row in set (0.00 sec)


mysql> SELECT * FROM tbl_name;
+------+
| a    |
+------+
|    2 |
|    4 |
|    1 |
|    3 |
|    5 |
+------+
5 rows in set (0.00 sec)

UPDATED

From MySQL Manual:

For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

Thanks to @Constantine.