CODEWITHSUNDEEP
mysqlindexing
PHAO THU
PHAO THU

Reputation: 135

Index in partitioned table

How MySQL create index for a partition table, Example if I create 5 hash by ID partitions:

Thanks

Upvotes: 0

Views: 4836

Answers (2)

Anshul Zunke
Anshul Zunke

Reputation: 69

Partitioned table in Mysql has only local indexes support. What does that mean? Every partition of the table stores its own B-Tree for the indexes. This can slow down the process of search if you don't have partition key as part of the index. Also for unique key constraint, you need to add partition key as part of the unique key.

Compared to Mysql, Oracle has concept of Global indexes as well. Global Indexes are very hard to manage.

I am not too sure How helpful Mysql partitions would be if it has ignored Global indexes.

Upvotes: 1

Rick James
Rick James

Reputation: 142316

There is no "global" index for a partitioned table in MySQL.

The only indexes you can put on a partitioned table ends up being separate indexes on each partition. Each partition is effectively an independent table.

HASH partitioning is virtually useless; do you have a particular use for which you think it might be beneficial?

Addenda...

The size of the index is similar to that of a table.

Since there are no "global" indexes, you cannot have a UNIQUE key unless it includes the column(s) of the "partition key". Nor can you use FOREIGN KEYs.

There is no type of index that spans more than one table.

Upvotes: 1

Related Questions