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!