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.

Answer 2

Just to add to Jason's answers, as per reference manual, you have following ways to get information about existing partitions on your table -

1. Using Show Create Table - to view the partitioning clauses used in creating a partitioned table; Syntax :
   show create table table_name;
   Sample Output :
   CREATE TABLE 'trb3' ( 'id' int(11) default NULL, 'name' varchar(50) default NULL, 'purchased' date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM )

2. Using Show Table Status - to determine whether a table is partitioned;
   Syntax:
   show table status in db_name like table_name;
   Sample Output: Shows lots of information about table like Name, Engine, Version, Data Length etc. You get value 'partitioned' for 'Create_options' parameter in output.

3. Querying the INFORMATION_SCHEMA.PARTITIONS table.
   (Refer Jason's answers, you can optionally add SUBPARTITION_NAME, SUBPARTITION_ORDINAL_POSITION, SUBPARTITION_METHOD, PARTITION_EXPRESSION etc Select parameters to get more information. Refer MySQL Ref Manual )

4. Using the statement EXPLAIN PARTITIONS SELECT - see which partitions are used by a given SELECT
   Syntax:
   EXPLAIN PARTITIONS SELECT * FROM trb1
   Sample Output:
   id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1,p2,p3 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using filesort

Read More At MySQL Ref Manual