MySQL Forums
Forum List  »  Partitioning

How are indexes partitioned on a partitioned table
Posted by: Peter Santos
Date: March 24, 2008 12:35PM

I'm relatively new to MySQL and I'm researching MySQL as a low cost alternative to Oracle for one of our systems. I've read enough documentation about how tables can be partitioned and the various partitioning methods, but what I have not seen is any information about how the indexes on partitioned tables are affected by the partitioning scheme.

My question is simply this. When an index is created on a partitioned table, are the indexes partitioned the same way as the table? In Oracle there are "local" and "global" indexes.
A local index is a partitioned index that has the same partitioned characteristics as the table. So for each partition there is a corresponding partitioned index .. and for administrative purposes they can be considered "atomic".

Global indexes on the other hand are not partitioned like the table, so any admin work on a particular partition could impact an index that spans other table partitions.

Does anyone know if how the indexes are handled. For example, if I have table that is range partitioned by month, and I create an index on the partition column. Will the index be partitioned by month .. or is it one big/global index?

The reason I'm asking is because when you manage table partitions you have to consider how it will impact the indexes on the tables .. if they are "local" to the table partition, then one usually can drop a partition a care, but not when the indexes are not locally partitioned.

Any thoughts would be appreciated.

Options: ReplyQuote

Written By
How are indexes partitioned on a partitioned table
March 24, 2008 12:35PM

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.