MySQL Forums
Forum List  »  Partitioning

Question regarding Partitioning by key
Posted by: kuku kuku
Date: August 28, 2011 03:47PM

Hi Guys.
I want to understand what means partitioning.
Reason:
lets say i have this table:(members)


mysql> select version() \g
+-----------+
| version() |
+-----------+
| 5.5.12 |
+-----------+
1 row in set (0.00 sec)


mysql> show create table members \G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`firstname` varchar(25) NOT NULL,
`lastname` varchar(25) NOT NULL,
`username` varchar(16) NOT NULL,
`email` varchar(35) DEFAULT NULL,
`joined` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (joined)
PARTITIONS 6 */
1 row in set (0.01 sec)

mysql> select count(*) from members \g
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.02 sec)

mysql> select * from members \g
+-----------+------------+-----------------+---------------------+------------+
| firstname | lastname | username | email | joined |
+-----------+------------+-----------------+---------------------+------------+
| BoBby | Adams | bodAdam | some@some.loc | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
| a | bbbbbbbbbb | ggggggggggggggg | admin@microsoft.com | 1314565548 |
+-----------+------------+-----------------+---------------------+------------+
9 rows in set (0.00 sec)

mysql> show table status like "members" \G
*************************** 1. row ***************************
Name: members
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 14
Avg_row_length: 7021
Data_length: 98304
Max_data_length: 0
Index_length: 0
Data_free: 56623104
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: partitioned
Comment:
1 row in set (0.02 sec)

mysql> explain partitions select datas.* from members as datas \g
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | datas | p0,p1,p2,p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 14 | |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

What i want to understand: If i execute this query:
mysql > select * from members \g
Mysql says:
9 rows in set (0.00 sec)

But when i execute this query:
mysql >explain partitions select datas.* from members as datas \g
Mysql says:

rows= 14

Is that rows temporary ot "virtual" ? Why i can't see this "hidden" rows?
For now i understand this something like this: May be that "hidden" rows are this partitions p0,p1,p2,p3,p4,p5 ?But in fact theris 6 partitions
p0 1'st
p1 2'nd
p2 3'rd
prior to p5 .?

Q: Guys please can someone explain to me why i can not see this hidden rows?

And Finally my second question:

Q: How i can obtain what data exist in p1 ?
Like this:
select * from p3
[Note where p3 means partition3]

Is it possible?


Thank you very much!

Options: ReplyQuote


Subject
Views
Written By
Posted
Question regarding Partitioning by key
2659
August 28, 2011 03:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.