Bug #20279 Can't select data from a specific partition
Submitted: 6 Jun 2006 5:23 Modified: 24 May 2012 11:35
Reporter: carlos mafla Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1.9-beta OS:Linux (CentOS release 4.2 (Final))
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[6 Jun 2006 5:23] carlos mafla
Description:
I created a hash partitioned table and i couldn't find a way to get data from a specific partition to know where the rows gone, not just to know what partitions a specific query access as the EXPLAIN PARTITION SELECT clause does.

How to repeat:
mysql> create table partition_hash
( col1 integer,
 col2 integer )
partition by hash(col1) partitions 4;

mysql> insert into partition_hash values (3,2),(5,4),(3,5);

mysql> select * from partition_hash;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+
3 rows in set (0.05 sec)

mysql> explain partitions select * from partition_hash;
+----+-------------+----------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table          | partitions  | type | possible_keys | key | key_len | ref  | rows | Extra |
+----+-------------+----------------+-------------+------+---------------+------+---------+------+------+-------+
| 1  | SIMPLE      | partition_hash | p0,p1,p2,p3 | ALL  |               | |         |      | 3    |       |
+----+-------------+----------------+-------------+------+---------------+------+---------+------+------+-------+

mysql> select * from partition_hash partition(p1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1)' at line 1
mysql> select * from partition_hash p1;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from partition_hash p2;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from partition_hash p3;
+------+------+
| col1 | col2 |
+------+------+
| 5    | 4    |
| 3    | 2    |
| 3    | 5    |
+------+------+

Suggested fix:
Allow the select from a specific partition
[4 Oct 2008 20:57] Konstantin Osipov
Subpartitions are not tables and can not be accessed independently from main tables.
If you would like to query an individual partition, please use a different partitioning (not by hash) and specify the partitioning clause in the WHERE.
[29 Jul 2009 13:24] Oli Sennhauser
Hi all

An other customer is requesting what they know from other RDBMS:

SELECT * FROM table (PARTITION #12);
[8 Sep 2010 12:35] Suresh Chary
I have a similar requirement. Did you get the answer to check the data partition-wise?
[15 Dec 2010 1:56] Meiji KIMURA
If you want to know 'count(*)' from specific partition like this,

SELECT count(*) FROM partition_hash PARTITION(p3);

you can use this query as alternative.

mysql> SELECT table_rows as 'count(*)' FROM information_schema.partitions WHERE table_schema = schema() and table_name =
'partition_hash' and partition_name = 'p3';
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)
[26 Jan 2011 22:09] Mattias Jonsson
This feature is being implemented as WL#5217.

Do not use the above workaround for InnoDB (I_S.PARTITIONS.table_rows), since it will not be correct, only an approximation. It will be exact for MyISAM though.
[24 May 2012 11:35] Mattias Jonsson
Added to MySQL 5.6:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html