Re: How are indexes partitioned on a partitioned table

[Jonathan Stephens]

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

MySQL Dev Zone
MySQL Server Documentation
Oracle