MySQL Forums
Forum List  »  Partitioning

Re: How are indexes partitioned on a partitioned table
Posted by: Jonathan Stephens
Date: March 25, 2008 04:07AM

Quote

If you have a table partitioned by month, and create an index on that table,
it will effectively be partitioned by month as well.

This is essentially correct. If you partition a MyISAM table, for example, you'll find that there are separate .MYD and .MYI files for each partition:

mysql> SHOW CREATE TABLE p.part1\G
*************************** 1. row ***************************
       Table: part1
Create Table: CREATE TABLE `part1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=1000016 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 8  */
1 row in set (0.12 sec)

mysql> exit
Bye
jon@flundra:~/bin/mysql-5.1/bin> cd ../var/p
jon@flundra:~/bin/mysql-5.1/var/p> ls part1*
part1.frm       part1#P#p0.MYI  part1#P#p2.MYD  part1#P#p3.MYI  part1#P#p5.MYD  part1#P#p6.MYI
part1.par       part1#P#p1.MYD  part1#P#p2.MYI  part1#P#p4.MYD  part1#P#p5.MYI  part1#P#p7.MYD
part1#P#p0.MYD  part1#P#p1.MYI  part1#P#p3.MYD  part1#P#p4.MYI  part1#P#p6.MYD  part1#P#p7.MYI

If you have innodb_file_per_table enabled, then you'll see something similar for partitioned InnoDB tables (one .ibd file per partition).

Jon Stephens
MySQL Documentation Team @ Oracle
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How are indexes partitioned on a partitioned table
8804
March 25, 2008 04:07AM


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.